timestamp help

tbobker

Well im trying to create a kind of community type website and have designed the database to insert a date everytime someone registers.

Im getting a little confused as to weather i should use a date field or int field and should i use the date() function or the mktime() function???

Basically one reason i want to insert a date is because i want to show the latest registered users on the front page. So i some how need to loop through the dates starting from the present back to a weeks time??? If that makes sense, im also struggling on how this could be done.

Im quite new to dates and times. Any help appreciated.

cahva

Use datetime() or timestamp() field in mysql to store those. You dont have to use PHP's date functions when you insert or fetch information from the database. Mysql has lot of very good date and time functions. Take a look:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

So for example when you are inserting new user you can use NOW() function:

INSERT INTO users (name,registered) VALUES ('John Doe',NOW())

That would insert user John Doe with current date and time(eg. 2007-07-10 12:45). Using unixtimestamps is just a pain in the arse. And even if you need to sometimes to have the date as unix timestamp, you can always convert that date to unix timestamp within mysql(check the link that I gave you and there "UNIX_TIMESTAMP" function).

EDIT: Oh, I almost forgot. You can find the answer to you question in that link.

tbobker

So if you just use a datetime field it stores the date in this format (2007-10-07) or simular.

But say i want to loop through my dates and return rows that are from todays date and the date a week ago. Im confused as to how i can make a query.

I have tried to do a while loop knowing that one day is 86400 seconds and trying to add this number onto the timestamp that a week ago would produce.

Does this make sense?

There are so many date time functions???

cahva

It depends how the field is saved. In this case its best to use DATETIME because you get the time also: 2007-07-10 14:42:12
With field type DATE you get only the date: 2007-07-10

You dont have to loop the entire user base to get the users from last 2 weeks. Thats what databases are all about! 🙂

You can get the users from last 2 weeks with this kind of query:

SELECT username FROM users WHERE registered > CURDATE() - INTERVAL 14 DAY

tbobker

Oh my god i cant believe it.....thats it to get the last 14 registered users..! Thanks.

ideffect

Actually

SELECT username FROM users WHERE registered > CURDATE() - INTERVAL 14 DAY

will retrieve the users that registered in the last 14 days.

cahva

Yes but I told that in my example that its two weeks. Original question was for a week but I misread it in a hurry. I think that tbobker got the point anyways 🙂

bradgrafelman

Don't forget to mark this thread resolved.