Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert MYSQL Timestamp to time_t

I'm writing a multi-threaded program that needs to be able to check if a row requires updating and act accordingly.

I had problems using the built in date/time functions of MySql and so decided to just store the "lastupdate" timestamp as an integer in the table. However, I'm having problems converting this timestamp to time_t so that I can use the time functions with it.

Any help is greatly appreciated.

like image 707
Kewley Avatar asked Jan 14 '10 20:01

Kewley


2 Answers

The MySql timestamp data type can be stored as a number in either YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format.

In Unix and POSIX-compliant systems, time_t is typically an integer which represents the number of seconds since the start of the Unix epoch: midnight UTC of January 1, 1970.

In MySQL you can use the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions convert between TIMESTAMP values and Unix timestamp values.

Query Example: SELECT Unix_Timestamp(Date_Entered) FROM Foo;

like image 76
jschmier Avatar answered Sep 30 '22 10:09

jschmier


Try FROM_UNIXTIME and TO_UNIXTIME and leave your dates as dates in the database.

like image 39
jmucchiello Avatar answered Sep 30 '22 10:09

jmucchiello