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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

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

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

date - INTERVAL 3 HOUR
potentially.
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

SOLVED

Post 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
Post Reply