CODEWITHSUNDEEP
mysqldatetimeseconds
Maxime
Maxime

Reputation: 43

MySQL convert seconds to datetime

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') ?

Upvotes: 4

Views: 18218

Answers (2)

Will B.
Will B.

Reputation: 18416

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

Upvotes: 1

Mihai Matei
Mihai Matei

Reputation: 24276

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

Here is an SQLFiddle

Upvotes: 4

Related Questions