Convert MYSQL Timestamp to time_t

c++ mysql datetime time-t
Kewley picture Kewley · Jan 14, 2010 · Viewed 7.9k times · Source

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

jschmier picture jschmier · Jan 14, 2010

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;

Related questions

mysql.h file can't be found

i'm trying to install connection between c++ and mysql in ubuntu 12.04. i've installed mysql-client, mysql-server, libmysqlclient15-dev, libmysql++-dev. but when i try to compile the code i got the error: mysql.h there is no such file. i looked …

Read More
C++ / mysql Connector - undefined reference to get_driver_instance - already tried the easy stuff

Yes this question has been asked before ... I've tried everything mentioned in the previous answers. My setup is really straightforward so this shouldn't be so hard. I just want to program against mysql using C++. My source code is taken …

Read More
How to access MySQL from multiple threads concurrently

We're doing a small benchmark of MySQL where we want to see how it performs for our data. Part of that test is to see how it works when multiple concurrent threads hammers the server with various queries. The MySQL …

Read More