What is the "official" DateTime stamp format

MySQL - went and changed?

[trillianjedi]

I've just been dumped on by a MySQL upgrade (3.23 to 4.1), which has changed all my datetime fields in the database which used to look like:-

20050101105403

.... for example (being 1/1/2005 10:54:03am) when returned as a string.

And now they are all "1/1/2005 10:54:03".

I don't really know anything about MySQL (perhaps this is a setting somewhere?), I just see the changes in the DB.

The problem is I was calling MySQL from a custom app that dumped some results to a text file for parsing, and all those parsing routines are now stuffed and need to be rebuilt.

I don't really want to go through this again - is there a "standard" timestamp format which I can use which is not going to get?

Alternatively, is there a way I can get MySQL to revert to the original format date/time stamps it was producing?

Thanks,

TJ

[Longhaired Genius]

I don't know much about MYsql either but there is bound to be a way to revert to your original timestamp, so don't do anything hasty. This may not be the best day to ask about it but be patient and I'm sure you'll get an answer.

[ergophobe]

Trill,

As of MySQL 4.1.0, TIMESTAMP columns are displayed in the same format as DATETIME columns.

I think these will help

[sourcekeg.co.uk...]
[sourcekeg.co.uk...]

One solution appears to be to essentially typecast in your select statement as

mysql> select mydate+0 from test;

See
[archives.neohapsis.com...]

This thread discusses the issue, but I'm not sure it will help you much

[forums.mysql.com...]

[coopster]

The TIMESTAMP literal is formatted (ie.e yyyy-mm-dd hh:mm:ss). Before upgrading, MySQL allows you to start the mysqld server with the "--new" command-line option to see how things will work in 4.1, but looks like we're too late for that.

As ergo stated, you can always cast it as a numeric and get the format you want.

I'm guessing though that if you are dumping the data for export you are more than likely doing a SELECT * ...

and now you are going to have to make a column list ...?

Additional Resources:
mysqld Command-Line Options [dev.mysql.com]
Overview of Date and Time Types [dev.mysql.com]

TIMESTAMP Properties Prior to MySQL 4.1 [dev.mysql.com]
TIMESTAMP Properties as of MySQL 4.1 [dev.mysql.com]

[Storyman]

I made the same MySQL upgrade, but decided that version 4.0 was a better upgrade for me. The biggest problem was having 4.1 on the testing server and 4.0 on the remote server. It isn't a smooth operation when creating, then backing up the testing server (4.1). The remote version (4.0) chokes on the newer database.

In other words use the same version on the testing server that is on the remote server.

IMHO

[trillianjedi]

Hey guys,

Many thanks for the comments and links - I've been doing some reading.

What I have is a Linux binary app that runs a query and dumps the result to a text file which I then parse, something like:-

select * from blah where blah > somefile.txt

I then take the tab delimited file and run through that (kind of complicated to explain in detail but I can't do this any other way).

The change in Date stamp format of course threw my app into a bit of a wobbly ;-)

I've actually just finished resolving the problem. I noticed that the new format is actually identical to the old with the exception of the added -'s in between dates and :'s in between times. And a space in the middle. So what I did was build a datetime string pre-parser which simply strips out any spaces, colons and dashes. That then gives me back the original format that I built my app. to deal with.

I think my big concern was that, if MySQL should change again, it'll all break again. But I guess I'll have to worry about that if it happens.

The above links have been really helpful to me though - I've learned a lot more than I originally expected to about MySQL this week!

Before upgrading, MySQL allows you to start the mysqld server with the "--new" command-line option to see how things will work in 4.1, but looks like we're too late for that.

Nice tip, but yes I'd blown it by this point. The problem was that something more important was broken that required the 4.1 upgrade, so we just had to go for it and didn't realise other things would break.

Ergophobe, in your link above I noted this comment:-

This change was necessary for SQL standards compliance.

So I think that actually answers my main concern. It seems that YYYY-MM-DD HH:MM:SS is the industry standard SQL syntax for a datetime stamp in string format. So that reassures me a bit that's it's not going to go changing again at least...

Thanks guys,

TJ