Handling Dates

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.

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.

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:

2 Thoughts to “Handling Dates”

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.

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.

Recent Posts

Posted in capacity as Module Leader Group Software Development, Department of Computing Sheffield Hallam University A Morgue One hundred students, 10 clients, 5 labs of University PCs, overzealous Facilities department and the Sheffield weather. What could possibly go wrong? Last week was ‘Scrum Week’. Nineteen groups of students working flat out 9-5, Monday to Friday…

Before reading the following ensure you are happy with the flexbox concepts of main axis and cross axis discussed in my previous post. Justification on the main axis The CSS property justify-content is used to control spacing between and around items along the main axis (assuming extra space is available). It is applied to the…

The concept of ‘flexible box’ or flexbox is name of the CSS3 flex layout model. Flexbox is based around flex containers and flex items. A flex container is a parent element to flex items. Flex items are the immediate children of the flex container and can be laid out in any direction. Take the following simple…