CODEWITHSUNDEEP
mysqldatetimeutcunix-timestamp
l33t
l33t

Reputation: 19966

Read MySQL datetime as Unix time

I have a table with a datetime column. Into this column I have written UTC dates using UTC_TIMESTAMP(). Now, from PHP, I want to get this UTC date as an unix timestamp. How?

I know of the UNIX_TIMESTAMP() function. I'm not sure it's reliable since it uses local time zone and stuff. I just want a pure conversion from the UTC datetime to the number of seconds since 1970...

Upvotes: 2

Views: 1605

Answers (2)

472084
472084

Reputation: 17895

SELECT UNIX_TIMESTAMP(datetime_field) as timestamp
FROM table

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

Upvotes: 3

Michael
Michael

Reputation: 12806

In PHP you'd use strtotime().

Edit: UNIX_TIMESTAMP uses local time zones when converting data stored as timestamp not data stored as datetime. See: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp and http://dev.mysql.com/doc/refman/5.5/en/datetime.html.

As you're using datetime, UNIX_TIMESTAMP should be safe.

Upvotes: 2

Related Questions