How can I get the total number of seconds since '1970-01-01 00:00:01' from a DateTime instance in MySQL?
Add a comment |
4 Answers
You are looking for UNIX_TIMESTAMP().
See: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_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.
2 Comments
Brethlosze
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.
Oleksandr
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."
Comments
SELECT DATE_FORMAT(`value`, '%Y%m%d') AS `date_ymd` FROM `table_name`; 
Comments
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)
