7

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!

Ben Kulbertis
  • 1,713
  • 4
  • 17
  • 30

1 Answers1

7

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
  • 337,827
  • 72
  • 505
  • 443
  • 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 Kulbertis Aug 15 '10 at 01:19
  • 1
    @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 Aug 15 '10 at 01:21
  • Ah, interesting. Thanks. I appreciate the help! – Ben Kulbertis Aug 15 '10 at 01:27