PHP - Store and get date/time value between PHP and MySQL

MySQL has three kinds of date/time attributes: DATETIME, DATE and TIMESTAMP.

• DATETIME starts from '1000-01-01 00:00:00' and ends up with '9999-12-31 23:59:59'. 
• DATE is part of DATETIME: YYYY-MM-DD
• TIMESTAMP starts from '1970-01-01 00:00:01' UTC and ends up with '2038-01-19 03:14:07' UTC. It is still fine today. However, do you still remember Y2P? Will TIMESTAMP be Y2K38P?

Generally speaking, you can use DATETIME for date like birthday, and use TIMESTAMP for anything related to current time. For example, when you add an article, you can use TIMESTAMP. 

In addition, since TIMESTAMP is timezone related, you will get a different value if your change your timezone. TIMESTAMP is also particularly useful in logging because it can be told to be updated on INSERTs and UPDATEs.

In summary, DATETIME represents a date (in calendar) and a time (on clock), while TIMESTAMP represents a well defined point in time.

PHP uses time() to return current Unix timestamp and date() to format a local time/date.

So, suppose we have a DATETIME column in MySQL, how we should store and get the value between PHP and database?

To Store

    
    //
    $datetime = date('Y-m-d h:i:s', time());
    // 2013-05-28 02:10:14

To Get - use strtotime() to parse any English textual datetime description into a Unix timestamp, and then use date() to change the timestamp to any format you would like to show.

    
    //
    $timestamp = strtotime($article["article_time"]);
    $time = date("l jS, F Y", $timestamp); 
    // Tuesday 28th, May 2013

Frequently used format in date().

d	Day of the month	01 to 31
j	Day of the month	1 to 31
S	English ordinal suffix for the day of the month	st, nd, rd or th. Works well with j
D	A textual representation of a day	Mon to Sun
l	A full textual representation of the day	Sunday to Saturday
m	Numeric representation of a month	01 to 12
n	Numeric representation of a month	1 to 12
M	A short textual representation of a month	Jan to Dec
F	A full textual representation of a month	January to December
Y	A full numeric representation of a year	1999 or 2003
y	A two digit representation of a year	99 or 03
a	Lowercase Ante meridiem and Post meridiem	am or pm
A	Uppercase Ante meridiem and Post meridiem	AM or PM
h	12-hour format of an hour	01 to 12
H	24-hour format of an hour	00 to 23
i	Minutes with leading zeros	00 to 59
s	Seconds, with leading zeros	00 to 59

Set your local timezone with date_default_timezone_set()

//
date_default_timezone_set('Australia/Adelaide');
//

Find all supported timezones at timezones.