Reputation: 90475

How to convert DateTime to a number in MySQL?

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

Upvotes: 18

Answers (4)

Reputation: 1612

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

Upvotes: 6

Reputation: 15431

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

Upvotes: 7

Reputation: 6494

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.

Upvotes: 36

Reputation: 78115

Upvotes: 8