How to convert DateTime to a number in MySQL?

Question

How can I get the total number of seconds since '1970-01-01 00:00:01' from a DateTime instance in MySQL?

score 36 · Accepted Answer · answered Dec 17 '09 at 13:01

36

You are looking for UNIX_TIMESTAMP().

If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC.

answered Dec 17 '09 at 13:01

Sven Lilienthal

That converts a Datetime, which in general do not need to be UTC. If you need to convert a local Datetime, this answer must be edited. Check my answer. – Brethlosze Jul 05 '16 at 20:05
Also, as documentation says "The valid range of argument values is the same as for the TIMESTAMP data type: '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0." – Oleksandr Jan 30 '21 at 17:33

score 7 · Answer 2 · answered Dec 17 '09 at 12:55

7

answered Dec 17 '09 at 12:55

martin clayton

score 7 · Answer 3 · edited Apr 16 '18 at 06:29

7

SELECT DATE_FORMAT(`value`, '%Y%m%d') AS `date_ymd` FROM `table_name`;

edited Apr 16 '18 at 06:29

Pang

answered Dec 17 '09 at 13:02

Orson

score 6 · Answer 4 · edited Nov 29 '21 at 06:50

6

UNIX_TIMESTAMP(datetime) force a localization of the datetime, which unlike the timestamp, is stored "as is".

You need actually any of the following, for discarding the UTC correction:

UNIX_TIMESTAMP(CONVERT_TZ(datetime, '+00:00', @@session.time_zone))

or:

TIMESTAMPDIFF(SECOND,'1970-01-01 00:00:00',datetime)

Refs: 1, 2, 3, 4

edited Nov 29 '21 at 06:50

Glorfindel

answered Jul 05 '16 at 19:52

Brethlosze

4 Answers4