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 mySQL 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

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

Answer 2

I've adapted the DATEDIFF workaround to also include time not just days. I've wrapped it up into a stored function, but you can just extract the SELECT part out if you don't want to use functions.

DELIMITER |
CREATE FUNCTION SIGNED_UNIX_TIMESTAMP (d DATETIME)
RETURNS BIGINT
 DETERMINISTIC
  BEGIN
    DECLARE tz VARCHAR(100);
    DECLARE ts BIGINT;
    SET tz = @@time_zone;
    SET time_zone = '+00:00';
    SELECT DATEDIFF(d, FROM_UNIXTIME(0)) * 86400 +
    TIME_TO_SEC(
      TIMEDIFF(
        d,
        DATE_ADD(MAKEDATE(YEAR(d), DAYOFYEAR(d)), INTERVAL 0 HOUR)
      )
    ) INTO ts;
    SET time_zone = tz;
    return ts;
  END|
DELIMITER ;

-- SELECT UNIX_TIMESTAMP('1900-01-02 03:45:00');
-- will return 0
-- SELECT SIGNED_UNIX_TIMESTAMP('1900-01-02 03:45:00');
-- will return -2208888900