Convert MYSQL Timestamp to time_t

Question

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.

Answer 1

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;

Answer 2

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