tags:

views:

41

answers:

1

I need to create a timestamp field for a table who's rows need to expire after a certain amount of time. If I use the following:

`timestamp` TIMESTAMP DEFAULT NOW(),

It shows the time in a human readable format, it would be a lot easier if I could have it in epoch time so I can calculate with seconds. Is there a way I can create a field that will display the current time when a row is created in epoch time by default? Thanks!

+3  A: 

You may want to use the UNIX_TIMESTAMP() function in your SELECT statements, as in the following example:

CREATE TABLE test_tb (`timestamp` TIMESTAMP DEFAULT NOW());
INSERT INTO test_tb VALUES (DEFAULT);

SELECT UNIX_TIMESTAMP(`timestamp`) epoch, `timestamp` FROM test_tb;
+------------+---------------------+
| epoch      | timestamp           |
+------------+---------------------+
| 1281834891 | 2010-08-15 03:14:51 |
+------------+---------------------+
1 row in set (0.00 sec)
Daniel Vassallo
Ah I see, so no way to have it entered that way by default. But I can use it in my `select` statements, better than nothing. Thanks Daniel! I will mark you as correct as soon as SO allows me.
Ben
@Ben: Actually, the `TIMESTAMP` data type stores dates in epoch format, according to the [docs](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-type-overview.html). However, the `SELECT` statement renders the value in "human readable form", by default. Using the `UNIX_TIMESTAMP()` function overrides this formatting.
Daniel Vassallo
Ah, interesting. Thanks. I appreciate the help!
Ben