List:General Discussion« Previous MessageNext Message »
From:Matthew Boulter Date:December 12 2003 6:57am
Subject:Converting char data ISO8601 to Unixtime Error
View as plain text  
Afternoon all,

This is an interesting problem that we ran into and have subsequently fixed (well it fixed

itself, but more on that later).
I posted a question to the list a couple days ago quoting this as a TIMESTAMP(14) column
issue, but as I investigated more, that turned out to be incorrect.

About 3 days ago, four of our tables started reporting incorrect date/times, they were all
out by 1 day in the future.
These errors were localised to the fields with a column type of TIMESTAMP(14), our only
TIMESTAMP(14) columns
in our database (save for a few in tables where they are used as a date/time marker, i.e.,
using the INSERT with NULL 
to set it to now).

As it turns out the issue seemed to be this:

MySQL added 1 day (24 hours) when converting from character data "YYYYMMDDHHMMSS" to
(seconds since epoch).

The effects were thus:

	* Doing an explicit INSERT INTO <Table> SET TimeStampField = '20031210235959'
	  Would put '20031211235959' into the table.

	* Doing a SELECT UNIX_TIMESTAMP('20031210233150') would return the unixtime of
	  In the example below, our time offset is UTC + 13hrs. The unixtime for '20031210233150'
should be 1071052310:

			mysql> SELECT UNIX_TIMESTAMP('2003-12-10 23:31:50');
			| UNIX_TIMESTAMP('2003-12-10 23:31:50') |
			|                            1071138710 |
			1 row in set (0.23 sec)

			mysql> SELECT FROM_UNIXTIME( 1071138710 );
			| FROM_UNIXTIME( 1071138710 ) |
			| 2003-12-11 23:31:50         |
			1 row in set (0.20 sec)

	* TIMESTAMP(14) fields where we never explicitly set the value, were unaffected.

	* There is no issue with mktime() at an OS level as alternative conversions using Perl
were correct.

	* We are running v3.23.49a on Red Hat Linux v7.2

While investigating this issue, the problem corrected itself!! We did nothing. Somehow 43
hours after the
problem arose, it suddenly went away, and we've spent the time since massaging the
affected records back to

Certainly a very strange problem and very disconcerting since it corrected itself without
any intervention on our 
part, it's making us ask "if it has happened before" and "will it happen again".

Just putting this one out there  :)
Matthew M. Boulter

Software Engineer
Saab ITS Pty Ltd

Mobile:	+61 (0)415 169 088
Phone:	+61 (0)7 3854-4815
Fax:	+61 (0)7 3854-4899 
Email:	matthew.boulter@stripped

Converting char data ISO8601 to Unixtime ErrorMatthew Boulter12 Dec