date conversions and MySQL unix_timestamp

Junior WebHelper Joined: 10 Oct 2002 Posts: 49

Thought I'd mention this so that others can avoid going thru time conversion mind-twisting conundrums as regards the MySQL UNIX_TIMESTAMP() function.

I have a database of dates in GMT for the start of ea season thru the year 2020. So, when I want to find out when the seasons start for a particular year, I was forming my queries as follows:

$query = 'SELECT UNIX_TIMESTAMP(`SPRING`), UNIX_TIMESTAMP(`SUMMER`) ';
$query .= 'UNIX_TIMESTAMP(`FALL`), UNIX_TIMESTAMP(`WINTER`)';
$query .= 'FROM `seasons` WHERE YEAR = $yr LIMIT 0, 30';

I wanted to be able to then display the date in GMT as well as in the local timezone for LA since I have an LA-centric site. So, for SPRING and WINTER I just used an offset of -8 to get the date/time for LA. This part worked just fine.

But, for SUMMER and FALL, when I applied the offset of -7, I kept getting the wrong result. Also, the GMT date info was wrong, too when I used gmdate with the UNIX_TIMESTAMP() results.

So, here's what I've finally been able to gather. Re MYSQL 3.23 - the UNIX_TIMESTAMP function if it takes a date parameter, it expects it to be in the local timezone. Since my db server is over in the UK near London, this meant that the date needed to be in GMT+1 to get valid results for SUMMER and FALL. So, here is the revised query:

$query = 'SELECT UNIX_TIMESTAMP(`SPRING`), UNIX_TIMESTAMP(`SUMMER`) + ONE_HOUR,';
$query .= 'UNIX_TIMESTAMP(`FALL`)+ ONE_HOUR, UNIX_TIMESTAMP(`WINTER`)';
$query .= 'FROM `seasons` WHERE YEAR = $yr LIMIT 0, 30';

NOTE: I elsewhere defined ONE_HOUR as 3600 (seconds).

Altho' some are opposed to using UNIX_TIMESTAMP() in MYSQL, I am going for it b/c the code I've written is fairly straightforward reading when the math is done by MYSQL.

One coding adventure down, on to the next. Very Happy

Team Member Joined: 06 Jan 2002 Posts: 2564

Thanks for sharing this with us php4ever Smile

.

Junior WebHelper Joined: 10 Oct 2002 Posts: 49

Thanks, Daniel!

Okay, I must confess that I spotted 2 errors in the above code Embarassed

ONE_HOUR won't work between single or double quotes b/c it is a defined constant. And, $yr within single quotes has the unintended value of '$yr' instead of a 4-digit year.

So, here's what actually works:

Code:

$query = 'SELECT UNIX_TIMESTAMP(`SPRING`), UNIX_TIMESTAMP(`SUMMER`) + ' . ONE_HOUR . ',';
$query .= 'UNIX_TIMESTAMP(`FALL`)+ ' . ONE_HOUR . ', UNIX_TIMESTAMP(`WINTER`)';
$query .= 'FROM `seasons` WHERE YEAR = ' . $yr . ' LIMIT 0, 30';