Drupal Dates in MySQL

Drupal stores all dates in the unix time format which is the number of seconds since the 1/1/1970. This is good because adding days, it easy, just add 86400 seconds, or you can use the strtotime().

When you are trying to access data directly from the MySQL database because it is not stored in a date field, it just appears as a number.

However there is a trick you can use in MySQL which will display this number as a date. You can use the FROM_UNIXTIME() which will convert the unix time to a MySQL date. So you can do something like

SELECT n.nid, FROM_UNIXTIME(n.created) FROM node n

This will display the created field as a date field. These times will be displayed in GMT, so you will still need to adjust it to your local time.

my projects: 

Comments

Using MySQL date example

Adding/subtracting a day when storing in a MySQL datetime is easy too;

SELECT NOW()- INTERVAL 1 DAY;

You can get MySQL to work with timezones too;

mysql> select now();

+---------------------+
| now()               |
+---------------------+
| 2006-06-09 14:54:10 |
+---------------------+
1 row in set (0.00 sec)

(I am in GMT+10) 

mysql> set @@session.time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2006-06-09 04:54:32 |
+---------------------+
1 row in set (0.00 sec)