Just a quick code tip for working with dates in mysql and php.
I’m building a script to regularly mirror my last.fm recent tracks to a mysql database of my own. I’m interested in experimenting with daily and hourly statistics.
One problem that has taken me some head scratching has been the fact that the last.fm recentracks web service gives me the date a given track was played in a timestamp of my current timezone (+02:00 , Europe/Paris, as I’ve just learnt), but my web server is on a different timezone (-05:00, America/Los_Angeles), so when I try to insert a date in the database, even though I’m giving it a timestamp, it gets interpreted as being 7 hours less than it actually is. So a track played at let’s say 31 Mar 2007, 23:55 would be stored as having been played at 31 Mar 2007, 16:55. Not good.
Not wanting to fiddle with configurations or anything beyond my knowledge, I discovered that I could set the timezone in both php and mysql for a given script or db query.
Setting the timezone in php
If you want to know which timezone your server is in, you can guess it with
date_default_timezone_get();
Which should give you a string like “America/Los_Angeles” or any other of the supported timezones list
If it is different than your desired timezone, look for the one you want in the list and before doing any time operation, call:
// set your timezone as gmt +02:00
date_default_timezone_set("Europe/Paris");
From then on, all your php code should understand timestamps and dates in that timezone.
Then for mysql use CONVERT_TZ
Even though it is corrected in php, you’ll have to do it also when inserting information on you mysql db, because it will interpret timestamps again in its time zone. You can guess which is it by having a look at the system time zone variable.
In my case, after trying some hacky alternatives I discovered CONVERT_TZ in one of the latest comments in this post
From the mysql manual
CONVERT_TZ(dt,from_tz,to_tz)
CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value.
You have to pass it the timestamp, current timezone (’SYSTEM’ is the server time zone) and target timezone and it happily converts between them.
So now my query would look like this:
$query = "INSERT INTO recenttracks (artist, name, url, date, type)
VALUES ( '$lastFmTrack->artist',
'$lastFmTrack->name',
'$lastFmTrack->url',
CONVERT_TZ(FROM_UNIXTIME(’$lastFmTrack->playDate’), ‘SYSTEM’, ‘+02:00?),
‘$lastFmTrack->type’)”;
Basically that’s it. Hope it is useful for someone.
Mind you, I’m nothing of an expert in php or mysql, this is just a method which worked for me. Corrections are welcome.
tags:date last.fm mysql php time timezones date, last.fm, mysql, php, time, timezones
. Initially inspired by some of my 
