Tag Archives: datetime

PHP: How to Convert a Date/Time from UTC to another Time Zone Programming 27 MAR 2015

Having previously shown you a useful way of quickly converting a timestamp into a formatted date string, here’s a quick way of actually getting a date/time in a time zone that you actually want – and again we turn to the powerfull PHP DateTime class to do this for us.

//here we'll use a UTC (time zone independent) time for our example
//(remember, time() always returns a UTC timestamp)
$dateTimeEnd = new DateTime('@' . time());
echo $dateTimeEnd->format('Y-m-d H:i:s');            
//now let's change to GMT+2 (SAST - South African Standard Time)
$dateTimeEnd->setTimezone(new DateTimeZone('Africa/Johannesburg'));
echo $dateTimeEnd->format('Y-m-d H:i:s');

As you can see, the setTimezone function of the DateTime class does all the heavy lifting for us, making it a snap to convert timestamps between different time zones.

Just a note, if you want to get the current time zone that your script is executing under, you can make use of date_default_timezone_get:

if (date_default_timezone_get()) {
    echo 'current time zone: ' . date_default_timezone_get() . '<br />';

A handy tip to remember then in other words!

world map showing time zones

Related Link: http://php.net/manual/en/class.datetime.php

PHP: How to Convert a Timestamp into a Date String Programming 26 MAR 2015

Unix timestamps aren’t exactly human readable representations of time. After all, a Unix timestamp is the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

Usually one wants to show this to a user in a much friendlier date time format, like the tried and trusted Y-m-d H:i:s standard, e.g. ‘2015-03-23 09:00’.

It turns out achieving this formatted display is relatively easy: just use the standard DateTime class!

You can use a timestamp as construct parameter if you add the @-sign at the front. Once that is done, simply use the format function to return a user friendly date/time string:

$dateTimeEnd = new DateTime('@1427105451'); 
echo $dateTimeEnd->format('Y-m-d H:i:s'); //prints 2015-03-23 10:10:51

Quick and simple.


Related Link: http://php.net/manual/en/class.datetime.php

PHP: Get the Number of Days between Two Dates Programming 22 AUG 2013

green php elephants - elephpantsA fairly common programming problem that crops up is calculating the number of days between two dates.

Prior to PHP 5.3 and all the goodness it added to the language, one usually set about calculating this simply by converting both dates to timestamps and then subtracting the one from the other, before finally converting the result into days which would of course be your answer.

Note that this was not particularly useful for all the guys out there using daylight savings time, but for guys like us in South Africa then it was perfectly fine!

In practice, this would have looked something like this:

$now = time(); // or your second date
$your_date = strtotime("2013-01-01");
$datediff = $now - $your_date;
echo floor($datediff/(60*60*24));

However, if you do have access to PHP 5.3, and really, you should by now, then you’ll be far better off using the functionality that the clever DateTime construct gives us:

$start_timestamp = time(); // or your second date
$end_timestamp = strtotime("2013-01-01");
$datetime1 = new DateTime(date('Y-m-d', $start_timestamp));
$datetime2 = new DateTime(date('Y-m-d', $end_timestamp));
$interval = $datetime1->diff($datetime2, true);
$days = intval($interval->format('%a'), 10); //or echo $interval->format('%R%a')


Bonus: And say that we wanted to be sure we have a full set of say 7 day periods for a trend graph, then we could use the above in something like this:

$datetime1 = new DateTime(date('Y-m-d', $start_timestamp));
$datetime2 = new DateTime(date('Y-m-d', $end_timestamp));
$interval = $datetime1->diff($datetime2, true);
$days = intval($interval->format('%a'), 10);
$extra = $days % 7;
if ($extra != 0) {
    $extra = 6 - $extra;
if ($extra != 0) {
    $start_timestamp = strtotime('-' . $extra . ' days', $start_timestamp);

That last one is more of a note to myself than anything else to be honest. Anyway, as per usual, play around with it on the useful PHP Code Pad.

MySQL: How add an Hour to a DateTime Column Value CodeUnit 29 AUG 2011

If you make use of a datetime column in your MySQL database to store timestamp data, here is a simple way of adding a couple of hours to your already stored value. You know, if the client suddenly decided to change time zone or something like that! ;)

UPDATE `table` SET `datetime-column` = DATE_ADD(`datetime-column`, INTERVAL 2 HOUR) WHERE `id` = 1

You can of course also minus hours by altering the above to add a negative interval:

UPDATE `table` SET `datetime-column` = DATE_ADD(`datetime-column`, INTERVAL -2 HOUR) WHERE `id` = 1

Simple and effective.

MySQL: Find All Records Submitted in the last Hour CodeUnit 19 DEC 2009

analog-clockIn the middle of writing a quick support page email notification system for Touchwork, I realised that I needed to add a check which would limit a user from submitting a query or bug report to a certain amount of entries per hour.

The easiest solution as it turned out was to run the check using a clever little SQL string, which looked like this:

SELECT * FROM `touchwork-support` WHERE DATE_SUB(NOW(),INTERVAL 1 HOUR) <= `ts-timestamp`

ts-timestamp is nothing more than a datetime column in the table, populated with the time of submission on every record submit. The SQL itself calculates the date time left over when subtracting an hour from the current date time, which is obviously then compared against the timestamp column to grab query-satisfying records.

With the result in hand, you simply check the num_rows value of the record set and if it is larger than the cut-off you imposed, return a suitable error message.

Couldn’t be easier! :)

For a more complete example:

$sql = “SELECT * FROM `touchwork-support` WHERE DATE_SUB(NOW(),INTERVAL 1 HOUR) <= `ts-timestamp`”;
$result = mysql_query($sql);
if (mysql_num_rows($result) >= 4)
echo ‘Oopsie!’;

MySQL: Get the Average Time Difference for Datetime Records Tips, Tricks and Tutorials 09 OCT 2009

If your table contains two datetime columns for which you wish to calculate, and display in readable format, the average time difference for the entire table, the simplest (and probably fastest) way to achieve this is by doing the calculation in your SQL statement directly.

To achieve this you first work out the time difference between the two fields using the TIMESTAMPDIFF functionality (specifying seconds as the unit to work in). Then you run AVG across that result set which will group and return the average time difference for the table as whole. Finally, use SEC_TO_TIME to convert the average result (which is now in seconds) into a user friendly display string.

Putting this all together, you SQL statement should now look like this:

“SELECT SEC_TO_TIME ( AVG ( TIMESTAMPDIFF ( SECOND, `datetime1`,`datetime2`))) FROM tabletoprocess

… the output of which will be in the format 00:00:00