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'));

There are two ways to find out the number of days for a given month. The first is to use the date() function in conjunction with the mktime() function to create a date and format this value as the number of days in a given month.

$monthDays = date("t",mktime(0,0,0,12,1,2008));

The second way is to use the function cal_days_in_month(). This function takes three parameters.

• Canelday: There are a number of different forms of calendar to chose from. The one most English speaking people are interested in is the CAL_GREGORIAN calendar. Take a look at a full list of calendar constands.
• Month: An integer representing the month (1 – 12).
• Year: An integer representing the year.

So to find out the number of days in the month of December of 2008 you could use the following.

$monthDays = cal_days_in_month(CAL_GREGORIAN, 12, 2008);

Use the following function to work out how many years have passed since an event. This can be useful if you want to work out a persons age based on their birthday.

The function works by standardising the format of the date using the PHP strtotime() function. This is the first step of the function and sorts out if the date is valid or not. Once this has been done then the date is formatted into a standard form of yyyy-mm-dd, which is then split using the explode() function. The year of the inputted date is then subtracted from the current year, giving the age in years. A final check makes sure that the date hasn’t passed yet, and subtracts one from the years value to give a more accurate result. Here is the function:

function dateToAge($birthday){
 if (($cleaned = strtotime($birthday)) === false){
  return false; // Not a readable format
 };
 
 list($year, $month, $day) = explode('-', date('Y-m-d', $cleaned));
 $age = date('Y') - $year;
 
 if (mktime(0,0,0,$month,$day,date('Y')) < mktime()){
  // Birthday hasn't passed, so subtract one year from age
  $age--;
 };
 
 return $age;
}

Here is an example of the function in use:

echo '01/20/1980'.birthday('01/20/1980'); // prints 27

There is one issue with this function is you cannot be sure that the date will be in a standard format. Some cultures put the date as day/month and others put it as month/day. This can make the function return nothing, even though the date is correct. So the following:

echo '20-01-1979 '.birthday('20-01-1980');

Returns false.

The following function sorts this problem out by trying to clean up the date entered so that it can be recognised by the program if it initially isn’t a valid date.

function dateToAge($birthday){
 if (($cleaned = strtotime($birthday)) === false){
  if(strpos($birthday,'/')!==false){
   $birthday = explode('/',$birthday);
   if(count($birthday)==3){
    $birthday = $birthday[1].'/'.$birthday[0].'/'.$birthday[2];
   };
  };
  if(strpos($birthday,'-')!==false){
   $birthday = explode('-',$birthday);
   if(count($birthday)==3){
    $birthday = $birthday[1].'-'.$birthday[0].'-'.$birthday[2];
   };
  };
  if (($cleaned = strtotime($birthday)) === false){
   return false; // Not a readable format
  };
 };
 
 list($year, $month, $day) = explode('-', date('Y-m-d', $cleaned));
 $age = date('Y') - $year;
 
 if (mktime(0,0,0,$month,$day,date('Y')) < mktime()){
  // Birthday hasn't passed, so subtract one year from age
 $age--;
 };
 
 return $age;
}