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

Answer 3

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 4

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 5

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 6

I have not tried the above solutions but this might in case you are not able to retrieve the date value from the MySQL database in the form of timestamp, then this operation can also be tried

SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00');

Answer 7

To get the max. Range +/- wise use this query on your birthday field, in my case "yyyy-mm-dd" but you can change it to your needs

select name, (@bday:=STR_TO_DATE(birthday,"%Y-%m-%d")),if(year(@bday)<1970,UNIX_TIMESTAMP(adddate(@bday, interval 68 year))-2145916800,UNIX_TIMESTAMP(@bday)) from people

Answer 8

I feel like we're making this much too difficult...

Use my functions below so you can convert anything to and from unix timestamps, much like you do in a browser.

Call functions like this:

select to_unix_time('1776-07-04 10:02:00'),
from_unix_time(-6106024680000);

By compiling these:

delimiter $$
create function to_unix_time (
    p_datetime datetime
) returns bigint
deterministic
begin
    declare v_ret bigint;
    
    select round(timestampdiff(
        microsecond,
        '1970-01-01 00:00:00',
        p_datetime
    ) / 1000, 0)
    into v_ret;
    
    return v_ret;
end$$

create function from_unix_time (
    p_time bigint
) returns datetime(6)
deterministic
begin
    declare v_ret datetime(6);
    
    select '1970-01-01 00:00:00' +
    interval (p_time * 1000) microsecond
    into v_ret;
    
    return v_ret;
end$$

Answer 9

Use Date instead of timestamps. Date will solve your Problems. check this link