converting ms access database to mysql 5 database

I've imported a ms access database into a table in a mysql database.
The access database contains a field that holds date/time values in
'general date' format. These all show up at 01/01/1970 in the mysql
database. I believe the field in mysql is wanting UTC and shows
numbers when looked at from the sql command line (i.e. March 13, 2006,
5:31 pm is shown as 1142289086). How do I get the access data into
that format so it will import properly?

Thanks for any help

Re: converting ms access database to mysql 5 database

"Dan Lewis" wrote in message
news:1142289238.674588.225400@i39g2000cwa.googlegroups.com.. .
> I've imported a ms access database into a table in a mysql database.

Have you checked out the MySQL Migration Toolkit, which is supposed to help
with this task?
http://dev.mysql.com/doc/migration-toolkit/en/index.html

> The access database contains a field that holds date/time values in
> 'general date' format. These all show up at 01/01/1970 in the mysql
> database.

Sounds like the values were truncated to zero during the import. This
stores the value 0 relative to the UNIX time epoch (1970-01-01 00:00:00
UTC).

> I believe the field in mysql is wanting UTC and shows
> numbers when looked at from the sql command line (i.e. March 13, 2006,
> 5:31 pm is shown as 1142289086).

I'm not sure what you mean here. MySQL is supposed to adjust time values
when you enter them, according the timezone you have configured for the
mysqld server or a per-connection timezone override. Then time values are
stored in UTC, and adjusted back to the server or connection timezone during
queries.

> How do I get the access data into that format so it will import properly?

MySQL date values must be strings in a limited number of specific formats,
e.g. 'YYYY-MM-DD HH:MM:SS'. See
http://dev.mysql.com/doc/refman/5.0/en/datetime.html for the legal formats.

You either need to export the data from MS Access in a MySQL-compatible
format, or else use the STR_TO_DATE function in MySQL to parse the MS Access
date strings into a valid MySQL date format. For example:

INSERT INTO myTable (dateField)
VALUES ( STR_TO_DATE('Monday, March 13, 2006', '%W, %M %e, %Y') );

See docs for DATE_FORMAT and STR_TO_DATE here:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html

Regards,
Bill K.

Re: converting ms access database to mysql 5 database

On 13 Mar 2006 in mailing.database.mysql, Dan Lewis wrote:

> I've imported a ms access database into a table in a mysql database.
> The access database contains a field that holds date/time values in
> 'general date' format. These all show up at 01/01/1970 in the mysql
> database. I believe the field in mysql is wanting UTC and shows
> numbers when looked at from the sql command line (i.e. March 13, 2006,
> 5:31 pm is shown as 1142289086). How do I get the access data into
> that format so it will import properly?

MySQL standard date format is yyyymmdd.

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-types.h tml

--
Joe Makowiec
http://makowiec.org/
Email: http://makowiec.org/contact/?Joe

Re: converting ms access database to mysql 5 database

I just found out that the mysql database is storing (actually, the
program that uses the db is storing) the date in epoch (unix date)
format in a long int field in the database. I've tried converting the
access field to a long int, but that didn't resolve the problem. How
do I convert a 'general date' formatted field to epoch? Will the MySql
migration kit do this? I loaded this, but didn't see how to map fields
from the source to fields in the destination.

TIA

Re: converting ms access database to mysql 5 database

"Wayne" wrote in message
news:1142330950.459146.177650@e56g2000cwe.googlegroups.com.. .
>I just found out that the mysql database is storing (actually, the
> program that uses the db is storing) the date in epoch (unix date)
> format in a long int field in the database. I've tried converting the
> access field to a long int, but that didn't resolve the problem. How
> do I convert a 'general date' formatted field to epoch?

See the UNIX_TIMESTAMP() function on page
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html.

Migrating data from one RDBMS system to another is often tricky. It may be
necessary to migrate the data into an interim table (comprised mostly of
VARCHAR columns), and then use various transformations and SQL functions to
get the values into the correct format for your final destination table.

Regards,
Bill K.