Home » Blogs » gordon's blog

Drupal Dates in MySQL

Posted by gordon on Tue, 09/05/2006 - 10:21am

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.


kids cosrumes

Submitted by sunny2010 (not verified) on Fri, 20/08/2010 - 7:24pm.

Come here to choose kids costumes to participate in cosplay party. coserfans.com have many cosplay costumes. If you have kids like cosplay, Kids costumes 100% hand made to fit your body. Now Many children were wearing cosplay costumes girls costumes! Now Cosplay costumes are: kids costumes, girls costumes, toddler costumes. While not cosplay originated in Japan, but the United States are subject to the Japanese, particularly popular kids costumes. COS also to dream about his childhood, kids costumes!

This beautiful and

Submitted by XmD (not verified) on Fri, 27/08/2010 - 7:51pm.

This beautiful and informative article, thank you very much. Use this page to your friend told you. I am constantly followed. To receive mp3 dinle more information about this topic the following internet sites can follow.

mp3 dinle muzik dinle

Submitted by mp3 dinle (not verified) on Wed, 11/08/2010 - 8:16am.

mp3 dinle muzik dinle

Sağlık Haberleri

Submitted by Sağlık Haberleri (not verified) on Wed, 11/08/2010 - 7:49am.

Thanks good article.. Thank you admin

sağlık haberleri
saglık
genel saglık
kalp damar hastalıkları
kanser hastalıgı ve tedavisi
kadın saglıgı ve gebelik
agız ve dis saglıgı
cinsel saglıkgüzellik ve estetik
psikolojik saglık
cilt saglıgı ve cilt sorunları
beslenme ve diyet metodları
egzersiz ve spor

منتديات

Submitted by alasiri (not verified) on Fri, 06/08/2010 - 9:54pm.

Thanks for that, I always get date and time mixed up

Using MySQL date example

Submitted by Morgan Tocker (not verified) on Fri, 09/06/2006 - 2:59pm.

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)

Typing Error?

Submitted by ozeMan (not verified) on Tue, 09/05/2006 - 9:20pm.
I think you meant to type "SELECT n.nid, FROM_UNIXTIME(n.created) FROM node n" rather than "SELECT n.nid, FROM_UNIXDATE(n.created) FROM node n"

Thanks for that, I always

Submitted by gordon on Sun, 14/05/2006 - 11:35pm.

Thanks for that, I always get date and time mixed up. I think it was all those years programming in PICK which they used a DATE() to return the number of dates, instead of time() which returned number of seconds.

Well, not more of that, just good old PHP. 

Actually, not necessarily

Submitted by merlinofchaos (not verified) on Tue, 09/05/2006 - 3:22pm.
Actually, not necessarily GMT, which is extremely annoying, but whatever MySQL thinks the timezone is. It's actually kind of annoying about that in that it's difficult (but not impossible) to tell MySQL to consistently give you GMT.