Comparing dates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Comparing dates

Post by jonas »

What's the best way to store and compare dates via DB entries.

Say we want to list the table groceries by most recent entries via the date column or compare 2 columns to see which is the most recent.

Where should I start with this?
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

order by date in your sql syntax?
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

Well, that's the easy part.

But let's say we want to prune a forum of posts that are older than 20 days.
So we say (this is in english not coding :)):
DELETE POSTS WHERE DATE >=20

Now how do you code that properly? Same with time, how do you compare time to say order posts by last_posttime > 50?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Store the time as the unix epoch and then get the current time and make the query:

Code: Select all

$result = mysql_query("SELECT * FROM posts WHERE time > '".(time()-(60*60*24*20))."'");
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

How do I store time as unix epoch... do you have php.net link so I could read up on it too? Thanks
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

Ok great, I'll use the unix timestamp then.

Now, once last thing before I continue on my forums...... what's the best way to make sure all my times inserted into the database are only a certain time zone?

I'm in EST and I want every date/time I input into the DB (and user inputted dates, even if they aren't in EST) to convert to EST.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you'll have to know the timezone the user is in, and "convert" it to your "normalized" time.

i.e. I'm in GMT-0700 lets say, and you want everything in GMT+0200, take the difference of the timezones.. 0900 (9 hrs), add that to the user's specified time.
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

this must have been that hard part you were telling me about when i said i was going to make my own forums

:) :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it gets worse :D :P
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

feyd wrote:it gets worse :D :P
Hahaha... true, true...

Dates was crap to learn (imho) and usually there is a couple of different approaches to the same goal.

Don't get caught only with what php has to offer but be sure to look up on the various inbuildt functions that MySQL has to offer aswell.
jonas
Forum Commoner
Posts: 96
Joined: Sun May 23, 2004 9:25 pm

Post by jonas »

My forums will be pretty basic but the times should all be the same.

I mean what if I post in EST and the timestamp uploads my topic timestamp as EST and someone posts a message in PDT?

That would mess up the times, wouldn't it?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the timestamp used, should be the server's time.. the information shouldn't be coming from the user.. only during output would you adjust the time displayed, so the times are local to that user..
Post Reply