FruitNotes beta

Your All-in-One Online Notebook

Good note on how to handle datetime in Mysql via PHP
Last updated at (Wed Jan 09 2008 15:37:28)

Nice note for reference in future

http://simonwillison.net/2003/Jul/11/storingDatesInMySQL/

Simon Willison’s Weblog

Storing Dates in MySQL

DevShed have a new article on Date Arithmetic With MySQL, which acts as a kind of missing manual for MySQL’s powerful date arithmetic functions. It reminded me of something I’ve been meaning to write about for some time: my thoughts on storing dates in a PHP application that uses a MySQL backend

MySQL comes with a full featured API for handling dates, and several column types for use with the date and time functions. Unfortuantely, none of the available column types map directly to PHP’s internal format for handling dates, the Unix timestamp (an integer value recording the seconds since midnight on January 1st, 1970). I have seen many PHP applications where people have used a MySQL integer field to store date information as a Unix timestamp. While this works, it is inadvisable as it prevents you from using any of MySQLs powerful built in date handling abilities.

Instead, you can use MySQL’s UNIX_TIMESTAMP() and FROM_UNIXTIME() functions to convert Unix timestamps to MySQL date types as part of your SQL queries. This allows you to use MySQL’s internal date manipulation features without having to manually convert MySQL dates to PHP timestamps in your PHP applications. I usually use DATETIME fields to store timestamps, but the conversion functions work for any of MySQL’s date storage types.

Here’s a sample select query:

SELECT 
entries.*, UNIX_TIMESTAMP(added) as unixtime
FROM 
enries
...

Assuming added is a DATETIME column, this adds an additional field to each returned row called ’unixtime’, containing an integer that can be passed straight to PHP’s handy date() function for formatting. Going the other way:

INSERT INTO 
entries
SET  
title = 'The title', 
added = FROM_UNIXTIME(1057941242),
...

The second example is less useful, but at least demonstrates the function. Incidentally, when inserting things in to a MySQL database with the current time it’s generally a good idea to use MySQL’s NOW() function to set the time, like this:

INSERT INTO 
entries
SET  
title = 'The title', 
added = NOW(),
...

Doing this increases consistency as it means that should you ever have a setup with multiple web servers talking to a single database server the database server’s time will be used as the standard, rather than potentially introducing errors from differing clocks on the server machines.

Posted 11th July 2003 at 5:40 pm

This is Storing Dates in MySQL by Simon Willison, posted on 11th July 2003.

2 blog reactions

Next: RSS Links

Previous: Stored procedures in MySQL?

28 comments

yes, my mind has always boggled at the number of comments submitted to the php manual with functions and tips for "parsing mysql's date format."

one thing to watch out for -- when using one of these types in a WHERE clause, it is best to do WHERE datecolumn = FROM_UNIXTIME(1057941242) and not WHERE UNIX_TIMESTAMP(datecolumn) = 1057941242. doing the latter won't take advantage of an index on that column.

jim winstead - 11th July 2003 19:03 - #
Regarding the use of NOW(), note that MySQL 4's query caching doesn't cache queries that contain that function (for good reason). To bypass this, I usually use something like $sql = 'UPDATE table SET added = "' . date('Y-m-d') . '" ...';

Adrian - 11th July 2003 19:13 - #
If the field is DATETIME, I have been able to format it any way with this line:
$formatdate = date("g:i A F j, Y",strtotime("$datetime"));
Stephen - 11th July 2003 20:34 - #
I used time stamp as well until I was building a genealogy application and I ran into a bug where everyone born before 1901 had their birth date as some random date, apparently the lower limit of 32-bit unix timestamps. The new way is messier probably with all the DATE_FORMATs but it works just fine and is very fast.
Matt - 11th July 2003 21:58 - #
For a very large table, I've decided to store unixtime (int(10)) as it is a more efficient use of space than the 14 byte MySQL timestamp. Doing without the conversion isn't a problem since PHP (and C) have methods to deal with unixtime already. I hope a new unixtime field will eventually be a part of MySQL.
Mark Richards - 6th November 2004 22:16 - #
What if I want to store a month and day in a mysql table. Say I have $ds = '7/6' and I want to store it in a field 'date_shipped' which is type date.
Dave Cone - 10th July 2005 00:50 - #
You don't now how many stupid hacks I've done because I didn't know how to convert between the two time formats! Only now, How can I enter a date into MySQL that is after 2038? I need to enter a date in 2051. Any chance of doing a tutorial that focuses on dates before 1900 and/or after 2038? Thanks!
Dotan Cohen - 23rd August 2005 06:21 - #
This is crap. What kind of two-bit system can't handle dates in the format that has been the stadard for 30 years. Open Source is clearly a defunct methodology for building software that won't go anywhere. Sell your stock now.
Nathan P. Clarke - 3rd October 2005 22:40 - #
Nathan, it simply takes a little more effort to deal with dates outside of a 'normal' date range. Considering the nature of your post, you display a surpruising amount of ignorance. Firstly, I seriously don't think that complaining about date functionality is any place for an advocate of proprietary software to start building an argument against open source. How quickly you forget the 'millenium bug', brought to us by your beloved Microsoft. This is merely a case in point, however. Proprietary software has arguably more 'bugs','features','holes' - call them what you will. The only difference is that proprietary software holes go unnoticed because source code is kept hidden away. If you understood the concept of Open Source, you would know that it is far from defunct. As an advocate, I would suggest that as time marches on, we will see that the open Source development methodology simply allows greater development, in terms of speed, stability, testing and results, than the proprietary mthodology ever could. Unless we all go and work for Microsoft.
Martin - 11th October 2005 14:25 - #
Thank you, php > mysql datestamps where doing my head in, your explanation worked a treat. Cheers
Paul - 9th January 2006 23:37 - #
Hey I am Money
mani - 21st January 2006 09:26 - #
What if I want to store just a MySQL DATE - that is, month-day-year - and not fuss with a time? Can I do this?
EE Jones - 9th February 2006 16:53 - #
Pardon my ignorance. My PHP form is putting timestamps very nicely into my table, formatted as dates times. Problem is, on this occasion I actually want the Unix timestamp in the field! What should I write in the form field to achieve this? Hoping one of you clever geezers can advise - thanks! C.
Cherryaa - 5th April 2006 19:23 - #
I am trying to change my website's Content Management System. My problem is that the the old database's date column is in the unix format but the new date column is in the datetime format. Is there a way I can ask the database to convert all the old ones to datetime format?
David - 10th May 2006 18:56 - #
FYI the DevShed article has moved to http://www.devshed.com/c/a/MySQL/Date-Arithmetic-W ith-MySQL/
Neill - 17th May 2006 04:54 - #
h
h - 29th May 2006 12:36 - #
Hi! I am a newbie in PHP and only know the most basic things in SQL. I like this information on the use of MySQL for the time consistency. In fact, I use this method in my site, which I am currently developing in my computer. But when I uploaded my site and my database to a web host, it's surprising that times are no longer correct (3 hours advanced)! Why is this?

Note that every update or insert on my database regarding the use of time, I used NOW().

Here's how I retrieve these timestamp data from MySQL:

SELECT *, UNIX_TIMESTAMP(timelog) AS unixtime FROM _blog;

(result set retrieval code goes here)

echo date("m/d/Y H:i", unixgmt(" 8", $rs[$i]['unixtime']))

//unixgmt is a custom function I made that adds the number of secounds given the hour

//offset in the 1st parameter. In my case, GMT 8, hence the 8.

In my personal computer server the output date and time is correct but when I tried on webhost, it is 3 hours advanced. What is wrong?

Andrei - 1st June 2006 13:48 - #
Andrei, the server that's hosting your site wouldn't be physically situated in a time zone three hours ahead of your location would it?
Bandolmer - 3rd June 2006 12:02 - #
Yes it is physically located elsewhere. But now I get it.
Andrei - 7th June 2006 20:20 - #
FROM_UNIXTIME() and UNIX_TIMESTAMP() are quite (load) expensive when compared with PHP's strtotime() and date() functions, especially when you consider that DB load is often more expensive than webserver load. I would recommend using strtotime() when retrieving dates from a mysql date or datetime field, and date('Y-m-d H:i:s') when passing your date to your string handler. Of course you still need to take care in PHP not to use timestamps for dates that occur before the epoch (eg. birthdays) or you can end up in all sorts of messes.
Marc Gear - 3rd July 2006 12:44 - #
Guyz, I love the page but I'm getting really confused here; Should i use the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions or the strtotime() and date() functions? What are the ups and downs of each of these pair of functions? Thnx a million
zync oxide - 2nd August 2006 08:46 - #
I was looking for a solution and this page has come up with 2. Perfect. The megawebs great!
Elliot - 16th August 2006 17:09 - #
A google search for 'php mysql dates' brought up this page with the perfect information. Kudos to you
Georgie - 21st August 2006 17:48 - #
You my friend are a prince. Just starting out with php, but would you believe I had two full nights trying figure out what you provided in two sentances. Cheers.
Shane - 28th August 2006 15:06 - #
I'm using FROM_UNIXTIME in a select on a timestamp field and converting it in php as : $tstr=date("M j Y h:i:s A",strtotime($r_ulog_date)); It's returning the wrong date (Dec 31 1969 instead of, correctly, Sep 1 2006). Am I missing something here?
Steve - 1st September 2006 21:20 - #
The link in your article to Date Arithmetic with MySQL stopped working somewhere along the line. Perhaps you want to update it, as I see quite a few folks are still getting good use out of this page. The link in my comment should do the trick.
AJ - 15th September 2006 00:55 - #
Here's a useful (java)script for soliciting dates from users in HTML Forms http://calendar.moonscript.com/dateinput.cfm It generates drop downs and a popup calendar inline in your forms. You can specify the format for populating the form field.
Russell Hutson - 20th September 2006 08:35 - #
The URI mentioned in the article no longer exists, the article can be found at http://www.devshed.com/c/a/MySQL/Date-Arithmetic-W ith-MySQL/
Bas Hamar de la Brethoniere - 16th October 2006 19:32 - #

FEEDBACKS

Leave your feedback below:

To start Your own Blog

Click Here