Page 1 of 1
Access to MySQL - Timestamp Conversion Issue
Posted: Sun Nov 23, 2003 3:34 pm
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:
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:
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?
Posted: Sun Nov 23, 2003 3:39 pm
by bionicdonkey
whenever i need to store time i use a unix timestamp
Posted: Sun Nov 23, 2003 3:44 pm
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?
Posted: Sun Nov 23, 2003 3:49 pm
by bionicdonkey
store it in a varchar field
Posted: Sun Nov 23, 2003 4:12 pm
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.
Posted: Mon Nov 24, 2003 2:19 am
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
Posted: Mon Nov 24, 2003 6:23 am
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?
Posted: Mon Nov 24, 2003 10:28 am
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