Page 1 of 1
Comparing dates
Posted: Fri Jul 16, 2004 7:55 pm
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?
Posted: Fri Jul 16, 2004 8:44 pm
by tim
order by date in your sql syntax?
Posted: Sat Jul 17, 2004 8:54 am
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?
Posted: Sat Jul 17, 2004 9:08 am
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))."'");
Posted: Sat Jul 17, 2004 7:16 pm
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
Posted: Sat Jul 17, 2004 7:53 pm
by John Cartwright
Posted: Sat Jul 17, 2004 10:46 pm
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.
Posted: Sat Jul 17, 2004 10:52 pm
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.
Posted: Sat Jul 17, 2004 11:33 pm
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

Posted: Sat Jul 17, 2004 11:39 pm
by feyd
it gets worse

Posted: Sun Jul 18, 2004 4:35 am
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.
Posted: Sun Jul 18, 2004 3:08 pm
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?
Posted: Sun Jul 18, 2004 3:14 pm
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..