4

So I have an integer '63605914755' like this:

SELECT TO_SECONDS( '2015-08-04 13:40:56' )
-----
Result: '63605914755'

How do I convert '63605914755' back from seconds to datetime (Ex: '2015-08-04 13:40:56') ?

Maxime
  • 43
  • 1
  • 1
  • 4
  • duplicated http://stackoverflow.com/questions/5385349/how-does-one-convert-seconds-or-milliseconds-to-a-timestamp-or-just-a-string - please read this. – Neil Aug 04 '15 at 06:47
  • I've read it but it didn't solve my problem. That's why I made this. Both UNIX_TIMESTAMP(63605914755) and FROM_UNIXTIME(63605914755) return NULL. – Maxime Aug 04 '15 at 06:52
  • https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp I would suggest using this - then hopefully it will work. – Neil Aug 04 '15 at 06:55
  • https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_makedate – Ivijan Stefan Stipić Aug 04 '15 at 07:16

2 Answers2

4
SELECT
  from_unixtime(seconds - to_seconds('1970-01-01 00:00:00')) as my_date_time
FROM 
  your_table

Here is an SQLFiddle

Mihai Matei
  • 24,166
  • 5
  • 32
  • 50
  • 2
    '63605914755' is seconds, it is NOT a UNIX TIMESTAMP. Your solution returned NULL. – Maxime Aug 04 '15 at 06:46
  • Be wary if your MySQL server has a timezone offset, this will cause the results of `FROM_UNIXTIME` to be off. The [timezone MySQL](https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html) uses by default is `SYSTEM`, but can be changed. – Will B. Jun 04 '19 at 19:05
1

Another approach is to simply add the number of seconds back to the base date of 0000-01-00. However, using 0000-01-00 will result in NULL so we have to specify the first day of the month 0000-01-01.
This results in the date being off by 1 day. To account for this we just subtract 1 day from the resulting date:

Example: https://dbfiddle.uk/YNJ7Q856

SELECT '0000-01-01' + INTERVAL TO_SECONDS('2015-08-04 13:40:56') SECOND - INTERVAL 1 DAY;

Results:

2015-08-04 13:40:56

To create a FROM_SECONDS function use:

DELIMITER //
CREATE FUNCTION `FROM_SECONDS`(
    `secs` BIGINT
)
RETURNS DATETIME
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    RETURN '0000-01-01' + INTERVAL secs SECOND - INTERVAL 1 DAY;
END//

SELECT FROM_SECONDS(TO_SECONDS('2015-08-04 13:40:46'));

As DATETIME values are points in time references (static), the above query can be DETERMINISTIC, since the resulting values of the supplied seconds will always be the same.

Note:
Since the the Unix Epoch date is based on UTC, when your MySQL server uses a timezone with an offset, using FROM_UNIXTIME(seconds - seconds_from_epoch), the resulting datetime will be off by that offset. The default timezone used by MySQL is the SYSTEM timezone.
For EST using FROM_UNIXTIME will result in 2015-08-04 09:40:56.

To account for this issue, you need to calculate the difference in minutes between UTC_TIMESTAMP and CURRENT_TIME and subtract the resulting minute offset from FROM_UNIXTIME. This will have no effect if your server uses UTC since the resulting offset will be 0.

SELECT FROM_UNIXTIME(TO_SECONDS('2015-08-04 13:40:56') - TO_SECONDS('1970-01-01 00:00:00')) - INTERVAL TIMESTAMPDIFF(MINUTE, UTC_TIMESTAMP(), NOW()) MINUTE;

Result:

2015-08-04 13:40:56
Will B.
  • 17,883
  • 4
  • 67
  • 69
  • Darn, this is ugly but correct, and the unit time limitations do not restrict it. It is annoying that MySQL has an incomplete set of functions. It offers to_seconds, which is a great function, but it does not offer the reverse function. This is by no means the only example where MySQL is functionally incomplete. – user9526573 Dec 13 '22 at 07:19