Access to MySQL - Timestamp Conversion Issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
vinyl-junkie
Forum Newbie
Posts: 23
Joined: Sat Nov 22, 2003 2:52 pm
Location: Seattle, WA area (USA)

Access to MySQL - Timestamp Conversion Issue

Post by vinyl-junkie »

OK, I've got 2 of 3 tables converted from MS Access to MySQL and have run into an interesting problem. I have a couple of timestamp fields in my csv file of the format:

Code: Select all

mm/dd/yyyy hh:mm:ss AM (or PM)
When the utility I'm running does the conversion of the csv file to MySQL, I get all zeroes in those timestamp fields. I think it's because the timestamp field in MySQL wants the format:

Code: Select all

yyyy-mm-dd hh:mm:ss AM (or PM)
So how would you recommend that I get these fields into the format that MySQL wants? Should I try to convert them to the correct MySQL format prior to converting the csv file to MySQL? Or would you recommend another way?
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

whenever i need to store time i use a unix timestamp
User avatar
vinyl-junkie
Forum Newbie
Posts: 23
Joined: Sat Nov 22, 2003 2:52 pm
Location: Seattle, WA area (USA)

Post by vinyl-junkie »

bionicdonkey wrote:whenever i need to store time i use a unix timestamp
But I'm converting a csv file to MySQL. I want to preserve the original timestamp. How's the best way to do that?
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

store it in a varchar field
User avatar
vinyl-junkie
Forum Newbie
Posts: 23
Joined: Sat Nov 22, 2003 2:52 pm
Location: Seattle, WA area (USA)

Post by vinyl-junkie »

bionicdonkey wrote:store it in a varchar field
OK, but then is there anything special I need to do when manipulating dates? What I mean here is that I have a web page that wants to select records that have been added to the database sometime within the last 30 days. Bear in mind as you answer that I'm a newbie to this stuff so you gotta lead me by the hand for a while anyway.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You could store it in a varchar field, but then you wouldn't be able to manipulate the date as a date which will be a problem. Is this a one time export from Access - or will you regularily be moving data across?

Mac
User avatar
vinyl-junkie
Forum Newbie
Posts: 23
Joined: Sat Nov 22, 2003 2:52 pm
Location: Seattle, WA area (USA)

Post by vinyl-junkie »

twigletmac wrote:You could store it in a varchar field, but then you wouldn't be able to manipulate the date as a date which will be a problem. Is this a one time export from Access - or will you regularily be moving data across?
I had thought about just storing it as a varchar field like you said, because I knew that would work. Unfortunately, this isn't just going to be a one-time export.

So just how is the best way to go about reformatting that timestamp so that MySQL would recognize it as such?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

The simplest solution I see is to convert your, say, '03-02-1999 01:02:03 PM', string to

Code: Select all

concat(mid('03-02-1999 01:02:03 PM',7,4),'-',mid('03-02-1999 01:02:03 PM',1,2),'-',mid('
03-02-1999 01:02:03 PM',4,2),mid('03-02-1999 01:02:03 PM',11))
using some external program such as awk or perl or php
Post Reply