Converting a date string which is before 1970 into a timestamp in MySQL.

Question

Not a very good title, so my apologies.

For some reason, (i wasn't the person who did it, i digress) we have a table structure where the field type for a date is varchar. (odd).

We have some dates, such as:

1932-04-01 00:00:00 and 1929-07-04 00:00:00

I need to do a query which will convert these date strings into a unix time stamp, however, in my sql if you convert a date which is before 1970 it will return 0.

Any ideas?

Thanks so much!

EDIT: Wrong date format. ooops.

Answer 1

convert these date strings into a unix time stamp

Traditional Unix timestamps are an unsigned integer count of seconds since 1-Jan-1970 therefore can't represent any date before that.

Answer 2

At best you will have mixed results depending on the system you are using to represent the timestamp.

From wikipedia

There was originally some controversy over whether the Unix time_t should be signed or unsigned. If unsigned, its range in the future would be doubled, postponing the 32-bit overflow (by 68 years). However, it would then be incapable of representing times prior to 1970. Dennis Ritchie, when asked about this issue, said that he hadn't thought very deeply about it, but was of the opinion that the ability to represent all times within his lifetime would be nice. (Ritchie's birth, in 1941, is around Unix time −893 400 000.) The consensus is for time_t to be signed, and this is the usual practice. The software development platform for version 6 of the QNX operating system has an unsigned 32-bit time_t, though older releases used a signed type.

It appears that MySQL treats timestamps as an unsigned integer, meaning that times before the Epoc will all resolve to 0.

This being the case, you always have the option to implement your own unsigned timestamp type and use that for your calculations.

Answer 3

If its feasible for your problem, you could shift all your mysql times by, say 100 years, and then work with those adjusted timestamps or re calculate the negative timestamp value.

As some have said, make sure your system is using 64bits to represent the timestamp otherwise you'll hit the year 2038 problem.

Answer 4

Aha! We've found a solution!

The SQL to do it:

SELECT DATEDIFF( STR_TO_DATE('04-07-1988','%d-%m-%Y'),FROM_UNIXTIME(0))*24*3600 -> 583977600
SELECT DATEDIFF( STR_TO_DATE('04-07-1968','%d-%m-%Y'),FROM_UNIXTIME(0))*24*3600 -> -47174400 

This could be useful for future reference.

You can test it here: http://www.onlineconversion.com/unix_time.htm

Voila! :D