Talk In Code

There are numerous ways to print out dates and times, and many hours of programming are spent in converting dates from one format to another.

To do this in MySQL you use the STR_TO_DATE() function, which has two parameters. The first parameter is the time to be parsed and the second is the format of that time. Here is a simple example that converts one date format to a MySQL formatted date string.

SELECT STR_TO_DATE('[21/Apr/2009:07:14:50 +0100]', '[%d/%b/%Y:%H:%i:%S +0100]');

This outputs 2009-04-21 07:14:50.

Using this function is quite intuitive and means that you can convert between time formats very easily. Here are a few more examples of this function.

SELECT STR_TO_DATE('21/04/2009', '%d/%m/%Y'); // 2009-04-21
SELECT STR_TO_DATE('04/21/2009', '%m/%d/%Y'); // 2009-04-21
SELECT STR_TO_DATE('2009-04-21 14', '%Y-%m-%d %H'); // 2009-04-21 14:00:00

If you enter a date that can’t be translated then you will get an error, take the following code that tries to convert a minute value of 87.

SELECT STR_TO_DATE('2009-04-21 14:87', '%Y-%m-%d %H:%i');

The following error message is returned.

Incorrect datetime value: '2009-04-21 14:87' for function str_to_date

This error can be quite easy to reproduce. For example, if you wanted to parse a time that was in 24 format, you would use the %H for hours. Using the %h value for hours will produce exactly this error.

The following table lists all of the different parameters that are involved in the date formatting features within MySQL. Use this to parse your own dates.

To format the current timestamp in W3C Datetime encoding (as used in sitemap.xml files) use the following parameters.

echo date('Y-m-dTH:i:sP', time());

As of PHP5 you can also use the c format character to print the exact same string.

echo date('c',time());

These would both print out the following:

2009-04-03T11:49:00+01:00

I have previously talked about using PHP to convert from a date to a time, but what about doing the same in JavaScript?

To get the unix timestamp using JavaScript you need to use the getTime() function of the build in Date object. As this returns the number of milliseconds then we must divide the number by 1000 and round it in order to get the timestamp in seconds.

Math.round(new Date().getTime()/1000);

To convert a date into a timestamp we need to use the UTC() function of the Date object. This function takes 3 required parameters and 4 optional parameters. The 3 required parameters are the year, month and day, in that order. The optional 4 parameters are the hours, minutes, seconds and milliseconds of the time, in that order.

To create the time do something like this:

var datum = new Date(Date.UTC('2009','01','13','23','31','30'));
return datum.getTime()/1000;


This prints out 1234567890. Here is a function to simplify things:

function toTimestamp(year,month,day,hour,minute,second){
 var datum = new Date(Date.UTC(year,month-1,day,hour,minute,second));
 return datum.getTime()/1000;
}


Notice that when adding in the month you need to minus the value by 1. This is because the function requires a month value between 0 and 11. However, there is an easier way of getting the timestamp by using the parse() function. This function returns the timestamp in milliseconds, so we need to divide it by 1000 in order to get the timestamp.

function toTimestamp(strDate){
 var datum = Date.parse(strDate);
 return datum/1000;
}

This can be run by using the following, not that the date must be month/day when writing the date like this.

alert(toTimestamp('02/13/2009 23:31:30'));

Or even this:

alert(toTimestamp('2009 02 13 23:31:30'));

Use the following function to work out how long it has been since an event in years, months, weeks, days, hours, minutes and seconds.

function getAge($year,$month,$day,$hour=0,$minute=0,$second=0){
 $age = mktime($hour,$minute,$second,$month,$day,$year);
 $age = time()-$age;
 return array('years'=>$age/60/60/24/365,
  'months'=>$age/60/60/24/12,
  'weeks'=>$age/60/60/24/7,
  'days'=>$age/60/60/24,
  'hours'=>$age/60/60,
  'minutes'=>$age/60,
  'seconds'=>$age);
}

The practical use of this function is that you can work out how old someone is from their birthday. Here is an example of the function in use.

// someone's birthday
echo '<pre>'.print_r(getAge(1984,10,4),true).'</pre>';

Array
(
 [years] => 23.721673198884
 [months] => 721.53422646605
 [weeks] => 1236.9158167989
 [days] => 8658.4107175926
 [hours] => 207801.85722222
 [minutes] => 12468111.433333
 [seconds] => 748086686
)

Any function like this can be tested by putting in the current time, if you get zero across the board then the function works.

The easiest (and most reliable) way to store the time in a database table is with a timestamp. It is also the most convenient way of working out time scales as you don’t have to do calculations in base 60. In MySQL this is accomplished by the UNIXTIME() function, which can be reversed by using another MySQL function called FROM_UNIXTIME().

However, you can sometimes be left with timestamps in your code and the task of trying to figure out what to do with them.

The first problem is trying to convert a timestamp into a date. So here is a PHP function that does this.

function timestamp($t = null){
 if($t == null){
  $t = time();
 }
 return date('Y-m-d H:i:s', $t);
}

And if you ever have a the opposite problem then here is a PHP function that converts a date string into a timestamp. At the moment the string needs to be in the format YYYY-MM-DD HH:MM:SS, which is what the previous function produced. This isn’t too difficult to change, just alter the parameters and order of the explode(‘ ‘, $str).

function convert_datetime($str) {
 
 list($date, $time) = explode(' ', $str);
 list($year, $month, $day) = explode('-', $date);
 list($hour, $minute, $second) = explode(':', $time);
 
 $timestamp = mktime($hour, $minute, $second, $month, $day, $year);
 
 return $timestamp;
}

Here is an example of the functions in use.

echo timestamp(convert_datetime('2008-05-10 20:56:00')). ' '. convert_datetime('2008-05-10 20:56:00') . ' 1210467360';