Page 1 of 1

Selecting records: DATETIME field<(NOW()-3 hours) ??

Posted: Wed Nov 15, 2006 6:14 pm
by maxd
In my MySQL table, I have a datetime field which stores the time at which a news release should be published to the Web site (the client sometimes posts releases to the database prior to official release, and sets the release date/time in the datetime field).

My problem is, I need to make accommodation for the Server's time being different than EST, which is the time zone the client relies on for announcement scheduling. My server is in Arizona, so there's a 3 hr. difference.

My SELECT statement currently looks like:

Code: Select all

SELECT id, headline, subhead, date_format(date, '%M %D, %Y') as displaydate FROM newsreleases WHERE YEAR(date)=$currentYear AND date <= NOW() ORDER BY date DESC
This works perfectly for Arizona time. Is there a simple way to subtract 3 hours from the NOW() time, so a release scheduled for 8 am would release at 5 am server-time?

Thanks,
max

Posted: Wed Nov 15, 2006 6:21 pm
by feyd

Code: Select all

date - INTERVAL 3 HOUR
potentially.

SOLVED

Posted: Wed Nov 15, 2006 11:56 pm
by maxd
BINGO! Thank you.

In the end, I used minutes, because the hour reference didn't work well with times like 8:30.

Here's how the code ended up:

Code: Select all

$sql = "SELECT id, headline, subhead, date_format(date, '%M %D, %Y') as displaydate FROM newsreleases WHERE YEAR(date)=$currentYear AND date - INTERVAL 180 MINUTE <= NOW() ORDER BY date DESC";
Again, thanks for your help.

max