There are a series of functions built into both PHP and MySQL which will help you when working with dates.

UNIX Timestamps

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).

Using strtotime()

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 [0] => 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:

d
Day of the month, 2 digits with leading zeros 01 to 31
D
A textual representation of a day, three letters Mon through Sun
j
Day of the month without leading zeros 1 to 31
l
(lowercase 'L') A full textual representation of the day of the week Sunday through Saturday
F
A full textual representation of a month, such as January or March January through December
m
Numeric representation of a month, with leading zeros 01 through 12
M
A short textual representation of a month, three letters Jan through Dec
n
Numeric representation of a month, without leading zeros 1 through 12
t
Number of days in the given month 28 through 31
Y
A full numeric representation of a year, 4 digits Examples: 1999 or 2003
y
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:

%a
abbreviated weekday name according to the current locale
%A
full weekday name according to the current locale
%b
abbreviated month name according to the current locale
%B
ull month name according to the current locale
%d
day of the month as a decimal number (range 01 to 31)
%D
same as %m/%d/%y
%e
day of the month as a decimal number, a single digit is preceded by a space (range ' 1' to '31')
%m
month as a decimal number (range 01 to 12)
%y
year as a decimal number without a century (range 00 to 99)
%Y
year as a decimal number including the century

Therefore:

<?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
?>

Date Picker

A popular way to ensure date accuracy is to use a date picker. With many of the Javascript date pickers available you can choose to have the date formatted in a MySQL friendly YYYY-MM-DD format.

Tip: The jQuery UI project offer an excellent javascript based date picker that can be customized for almost any purpose.

HTML5 Form Fields

Although browser support is not yet consistent HTML5 offers a new form field type of date. In some browsers this will automatically produce a date picker with no need to use PHP or indeed Javascript.

<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:

%a
Abbreviated weekday name (Sun..Sat)
%b
Abbreviated month name (Jan..Dec)
%c
Month, numeric (0..12)
%D
Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d
Day of the month, numeric (00..31)
%e
Day of the month, numeric (0..31)
%M
Month name (January..December)
%m
Month, numeric (00..12)
%Y
Year, numeric, four digits
%y
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
or
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'.

phpmyAdmin-autodates

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;

2 Thoughts to “Handling Dates”

  1. Another amazing section thanks again. Ran into an issue with an inherited mysql database where the date is string varchar and to analyze the data i obviously need date format. Finally through your examples found a way to get all my current strings switched over and am going to run a 1 time php script to update over 2000 entries. Then phpmyadmin should let me set to a date type without the "not a number" error. One thing i do not see is what to do about am and pm. For instance my current strings are stored exactly as
    example : "12/29/2016 04:30pm". per mysql it needs to be "2016-12-29 16:30".
    Is there anyway to grab the pm and am's and somehow get that info to out put as 24hour time? I don't know how to convert that part at all, if need be i know hours of operation are not before 7am and not after 7pm so i can phpmyadmin and manually set to a 24hour setup, but the lesson and practice would be paramount and worth the experience.

    1. Sorry to self answer but this is very cool the am and pm seem to be automatically understood and processed in php so if you select H as your hour it will read your am and pm and attach time. Here is my example.
      ~~dtob is a variable pulled from the database using foreach however i shown it as static for the example
      ------------------------------------------------------------------code
      $dtob =12/30/2016,09:08pm |
      $mydate = strtotime("$dtob"); |
      echo date("Y m d H:i ", $mydate); |
      ------------------------------------------------------------------
      ------------------------------------------------------------------output
      2016 12 30 21:08 |
      ------------------------------------------------------------------
      id say if your stuck with a sql database that is using the date column with a string value you can use this just attach the output to a variable and "update" rather than echo. If you force your database to "date" datatype and your strings are not in the correct format you will basically erase all of your dates to 0000-00-00. Trust me i had to backup and reload. But you can swap whenever you like as long as the string is correctly formatted without issue. I know I am not the first, or will be the last to have this database issue so hopefully this helps someone get a start.

Leave a Comment