473,372 Members | 1,076 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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

Mar 13 '06 #1
4 3301
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/...ime-types.html

--
Joe Makowiec
http://makowiec.org/
Email: http://makowiec.org/contact/?Joe
Mar 14 '06 #2
"Dan Lewis" <wa********@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.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-t.../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/...functions.html

Regards,
Bill K.
Mar 14 '06 #3
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

Mar 14 '06 #4
"Wayne" <wa********@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.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/...functions.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.
Mar 14 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Mike Doanh Tran | last post by:
Hi all, Does anyone have any suggestion for converting a Sequel database to MYSQL? I just want to copy a Sequel database data to a Mysql one. Thanks in advance for any suggestion, Mike --
17
by: chicha | last post by:
Hey people, I have to convert MS Access 2000 database into mysql database, the whole thing being part of this project I'm doing for one of my faculty classes. My professor somehow presumed I...
2
by: John | last post by:
I'd love to hear some expert opinion on my situation. My department relies on Excel as a database, which causes crashing due to resource depleting and basically is just too slow. For example, a...
7
by: RCS | last post by:
Okay, a rather 'interesting' situation has arisen at a place I work: I need to convert a database from Access to something that can be used over the web. I am currently maintaining and...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
4
by: Bob Alston | last post by:
Anyone have experience with converting an access app from Jet database to Mysql? I am specifically looking for any changes I would have to make to my access forms, queries, modules, vba code, etc....
8
by: Michael B. Trausch | last post by:
I was wondering if anyone has had any experience with this. Someone I know is trying to move away from Microsoft Works, and I am trying to look into a solution that would convert their data in a...
28
by: Randy Reimers | last post by:
(Hope I'm posting this correctly, otherwise - sorry!, don't know what else to do) I wrote a set of programs "many" years ago, running in a type of basic, called "Thoroughbred Basic", a type of...
4
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.