There are a series of functions built into both PHP and MySQL which will help you when working with dates.
Where were you on Friday 13th February 2009 around half past four? This momentous date was when the UNIX timestamp reached the figure of 1234567890 – and boy did we party.
If you feel you are missing something here let me explain a little about the UNIX timestamp and why it is important in the world of PHP. The UNIX timestamp represents the number of seconds that have passed since the 1 January 1970 and for PHP it is the only way it really understands time and dates. However, PHP has lots of ways to manipulate and represent this number in all the many ways we do with dates and time.
Creating Timestamps with time(), mktime() and gmmktime();
The function time() returns an integer which is a UNIX date/time stamp.
<?php echo time(); //outputs something like 1139328834 ?>
The function mktime() can be feed integers for the hour, minute, second, month, day and year for the required timestamp. The output would still be an unfriendly integer representing the seconds since 1 January 1970.
<?php $mydate1 = mktime(1,30,50,12,13,1971); echo $mydate1; //outputs 61435850 the number of seconds since 1 January 1970 ?>
The function gmmktime() works the same as mktime() but whereas mktime() uses local time gmmktime() uses Greenwich Mean Time (GMT).
The function strtotime() can be used to create timestamps between December 13 1901 and January 19 2038. The function accepts dates in the format “05 DEC 2004” and “12/05/2004”.
Warning: Note the use of the American style of shorthand date ie 12/05//2011 for 5 December 2011.
<?php $mydate = strtotime("5 MAY 2006"); echo $mydate; ?>
This is useful when you are allowing the user to enter dates and need to compare it with another timestamp.
Getting the Current Date/Time with getdate()
The getdate() function will output the current date/time. The function takes a timestamp value as a parameter. If not is provided the current date is returned.
<?php $today = getdate(); print_r($today); ?>
Notice that the timestamp can be output as an array.
Array ( [seconds] => 8 [minutes] => 4 [hours] => 14 [mday] => 8 [wday] => 3 [mon] => 2 [year] => 2006 [yday] => 38 [weekday] => Wednesday [month] => February  => 1139407448 )
A timestamp is an associate array.
The strtotime() function can also be used to get the current time
<?php $currentdate = strtotime("now"); echo $currentdate; ?>
The date is still in integer format.
Formatting a Date with date() and strftime()
The integer format of the timestamp is not particularly user friendly. Therefore there are various techniques to format date output.
The date() function takes a timestamp and formats it according to user defined format characters.
<?php $mydate = strtotime("1/31/80"); echo date("D d M Y", $mydate); // outputs Thu 31 Jan 1980 ?>
Headline format characters are:
- Day of the month, 2 digits with leading zeros 01 to 31
- A textual representation of a day, three letters Mon through Sun
- Day of the month without leading zeros 1 to 31
- (lowercase ‘L’) A full textual representation of the day of the week Sunday through Saturday
- A full textual representation of a month, such as January or March January through December
- Numeric representation of a month, with leading zeros 01 through 12
- A short textual representation of a month, three letters Jan through Dec
- Numeric representation of a month, without leading zeros 1 through 12
- Number of days in the given month 28 through 31
- A full numeric representation of a year, 4 digits Examples: 1999 or 2003
- A two digit representation of a year Examples: 99 or 03
Taking our earlier example
<?php $mydatetest = strtotime("1/31/80"); echo date("l j F Y", $mydatetest); // outputs Thursday 31 January 1980 ?>
In addition the strftime() function can be used for date formatting. Like date() this takes formatting rules (known as conversion specifiers) and a timestamp.
Headline conversion specifiers are:
- abbreviated weekday name according to the current locale
- full weekday name according to the current locale
- abbreviated month name according to the current locale
- ull month name according to the current locale
- day of the month as a decimal number (range 01 to 31)
- same as %m/%d/%y
- day of the month as a decimal number, a single digit is preceded by a space (range ‘ 1′ to ’31’)
- month as a decimal number (range 01 to 12)
- year as a decimal number without a century (range 00 to 99)
- year as a decimal number including the century
<?php $mydatetest2 = mktime(1,30,50,12,25,1986); echo strftime("%d %B %Y", $mydatetest2); //outputs 25 December 1986 ?>
Validating Dates with checkdate()
Use checkdate() to check if date is a valid Gregorian date. Returns a boolean true/false. The date is passed to checkdate() in the format
checkdate( int month, int day, int year )
This is useful when allowing users to enter dates as it will catch incorrect dates such as 31 Feb 2012 and leap year adjustments.
<?php echo checkdate('2', '31', '2001'); //no output as false date – no date 31 Feb echo checkdate('1', '31', '2001'); //outputs 1 to indicate true – yes 31 Jan valid ?>
HTML5 Form Fields
Although browser support is not yet consistent HTML5 offers a new form field type of
<input type="date" name="startDate">
This is what your browser makes of it:
Outputting Dates From MySQL
In MySQL dates are stored in the date format YYYY-MM-DD ie 2012-11-30. This is not a particularly user friendly output format.
There are two ways to approach this. Either format the date via PHP or via the SQL statement.
Using PHP to Format MySQL Dates
To format a date from a database query we could use two of the date functions in combination.
The strtotime() function can be used to convert the YYYY-MM-DD format from MySQL into a timestamp.
<?php $timestamp_date = strtotime($database_date); ?>
Then the timestamp could be formatted with the date() or the strftime() function.
<?php $display_date = date("D d M Y", $timestamp_date); ?>
This could be combined in one line.
<?php $display_date = date("D d M Y", strtotime($database_date)); ?>
Using SQL to Format MySQL Dates
In SQL the DATE_FORMAT() function can be used. The function is wrapped around a valid date field name from the database and feed formatting rules. In the SQL this formatted date should be allocated with an ‘AS’ to a new field name for outputting.
$query = "SELECT DATE_FORMAT(release_date, '%d/%m/%Y') AS df_release_date, product_name FROM products";
Like the PHP functions strftime() and date() the DATE_FORMAT function accepts specifers. Headline specifiers are:
- Abbreviated weekday name (Sun..Sat)
- Abbreviated month name (Jan..Dec)
- Month, numeric (0..12)
- Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
- Day of the month, numeric (00..31)
- Day of the month, numeric (0..31)
- Month name (January..December)
- Month, numeric (00..12)
- Year, numeric, four digits
- Year, numeric (two digits)
As such the following:
SELECT DATE_FORMAT(release_date, '%D %b %Y') AS df_release_date, title FROM videos;
Would format the date as:
30th Nov 2011
Querying by Dates in MySQL
When querying a date in a MySQL database we need to place the date in the format:
YYYY-MM-DD ie 1999-05-30
YY/MM/DD ie 99/5/30
Warning: The date must be treated as a string in the SQL statement
Inputting Dates into MySQL
As with querying by date, the date chosen by the user needs to be in the correct format for MySQL. Notice how in the mysqli example below how the prepare statement treats the date as a string.
$filmDate = "2013-01-31" //YYYY-MM-DD $stmt = $mysqli->prepare("INSERT INTO movies(filmRelease) VALUES (?)"); $stmt->bind_param('s', $filmDate); $stmt->execute(); $stmt->close();
To generate a MySQL friendly timestamp using PHP we can use the
date() method ie:
$thisDate = date('Y-m-d'); echo $thisDate; // output as YYYY-MM-DD
If you want the time as well then use:
$thisDate = date('Y-m-d h:i:s'); echo $thisDate; // output as YYYY-MM-DD H:M:S
Tip: If you want to use GMT then use the
gmdate() variant of the
date() method. It works exactly the same but returns the date/time according to Greenwich Mean Time (GMT)
Date Stamps in MySQL
Often with a database it is useful to have a field that represents the last time (and date) when a particular field was updated. This can be done via phpMyAdmin. When creating your field in the database create it as a datatype of ‘timestamp’.
Then edit this field in phpMyAdmin and you will see the option to set a default value to ‘CURRENT_TIMESTAMP’. This will mean that when a new record is created the current date/time is entered automatically to that field. In addition, under the attribute option for that field, you can choose ‘ON UPDATE CURRENT_TIMESTAMP’. This will ensure that when the record is updated with a SQL
UPDATE statement, the field is automatically updated – so you don’t need to send a new date value through to the database – neat.
Installations of phpMyAdmin vary so if you don’t see the option above you can run the following SQL to add a current timestamp when a field is updated:
ALTER TABLE `yourtable` CHANGE COLUMN `yourlastUpdatedField` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;