4WebHelp
 FAQ  •  Search  •  User Groups  •  Forum Admins  •  Smilies List  •  Statistics  •  Rules   •  Login   •  Register
Toggle Navigation Menu

 date conversions and MySQL unix_timestamp
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic
Author Message
php4ever
Junior WebHelper
Junior WebHelper


Joined: 10 Oct 2002
Posts: 49

PostPosted: Tue Aug 03, 2004 12:27 am (13 years, 4 months ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by php4everSend Personal Message
Daniel
Team Member



Joined: 06 Jan 2002
Posts: 2564

PostPosted: Thu Aug 05, 2004 6:25 am (13 years, 4 months ago) Reply with QuoteBack to Top

Thanks for sharing this with us php4ever Smile.

________________________________
Image
OfflineView User's ProfileFind all posts by DanielSend Personal Message
php4ever
Junior WebHelper
Junior WebHelper


Joined: 10 Oct 2002
Posts: 49

PostPosted: Fri Aug 06, 2004 7:22 am (13 years, 4 months ago) Reply with QuoteBack to Top

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';
OfflineView User's ProfileFind all posts by php4everSend Personal Message
Display posts from previous:      
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic


 Jump to:   




You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot edit your posts in this forum.
You cannot delete your posts in this forum.
You cannot vote in polls in this forum.


Page generation time: 0.051025 seconds :: 17 queries executed :: All Times are GMT
Powered by phpBB 2.0 © 2001, 2002 phpBB Group :: Based on an FI Theme