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
jonas
Forum Commoner
Posts: 96 Joined: Sun May 23, 2004 9:25 pm
Post
by jonas » Fri Jul 16, 2004 7:55 pm
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?
tim
DevNet Resident
Posts: 1165 Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio
Post
by tim » Fri Jul 16, 2004 8:44 pm
order by date in your sql syntax?
jonas
Forum Commoner
Posts: 96 Joined: Sun May 23, 2004 9:25 pm
Post
by jonas » Sat Jul 17, 2004 8:54 am
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 » Sat Jul 17, 2004 9:08 am
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 » Sat Jul 17, 2004 7:16 pm
How do I store time as unix epoch... do you have php.net link so I could read up on it too? Thanks
jonas
Forum Commoner
Posts: 96 Joined: Sun May 23, 2004 9:25 pm
Post
by jonas » Sat Jul 17, 2004 10:46 pm
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sat Jul 17, 2004 10:52 pm
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 » Sat Jul 17, 2004 11:33 pm
this must have been that hard part you were telling me about when i said i was going to make my own forums
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sat Jul 17, 2004 11:39 pm
it gets worse
JAM
DevNet Resident
Posts: 2101 Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:
Post
by JAM » Sun Jul 18, 2004 4:35 am
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 » Sun Jul 18, 2004 3:08 pm
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?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Sun Jul 18, 2004 3:14 pm
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..