Page 1 of 1
Best way to store a date and time in a database?
Posted: Mon Nov 06, 2006 6:04 pm
by Mr Tech
What is the best way to store a date and time in a database? What is the most flexible?
Is using the time() function? Or the date() function to display something like 2006-11-7 13:02?
The sort of things I'm wanting to do is show records between certain dates, draw from database into a calendar etc.
Posted: Mon Nov 06, 2006 6:21 pm
by Luke
I like storing unix timestamps... because it makes it easier on the PHP side (for me anyway). I believe it's easier to work with YYYY-MM-DD HH:MM:SS when it comes to mysql
Posted: Mon Nov 06, 2006 6:53 pm
by Mr Tech
If you did use YYYY-MM-DD HH:MM:SS, how would you select from the database between two dates?
Posted: Mon Nov 06, 2006 6:59 pm
by Luke
I think it's as simple as...
Code: Select all
SELECT * FROM `my_table` WHERE date BETWEEN '2005-04-23 00:15:00' AND '2006-11-21 10:00:00'
EDIT: OK, so this should be correct now...

Posted: Mon Nov 06, 2006 7:12 pm
by feyd
remember quotes around those otherwise it's math to the database

Posted: Tue Nov 07, 2006 9:55 am
by pickle
I used to be a hard-core UNIX timestamp man. Ever since I found FROM_UNIXTIME() (for converting a UNIX to MySQL timestamp) and UNIX_TIMESTAMP() (for doing the opposite), MySQL stamps have become more appealing - primarily because they're easier to read from the command line. If you don't see yourself going to the command line very often, I'd stick with a UNIX timestamp because like ~Ninja said - they're easier to work with in PHP.
Posted: Tue Nov 07, 2006 10:05 am
by Luke
yea the only thing that really bugs me about timestamps is that when I look at records in the database via phpmyadmin, I can't tell immediately what the dates are... I have to convert them to human-readable format before I know... which kinda sucks when you're just trying to see the time and date of a record real quick.
Posted: Tue Nov 07, 2006 12:44 pm
by timvw
I prefer the DATE or DATETIME type... Simply because it covers much more dates than a unixtimestamp....
Posted: Tue Nov 07, 2006 12:47 pm
by Oren
timvw wrote:I prefer the DATE or DATETIME type... Simply because it covers much more dates than a unixtimestamp....
I'll second that.
Posted: Tue Nov 07, 2006 12:58 pm
by Burrito
Oren wrote:timvw wrote:I prefer the DATE or DATETIME type... Simply because it covers much more dates than a unixtimestamp....
I'll second that.
and I'll third....
Posted: Tue Nov 07, 2006 8:09 pm
by feyd
Burrito wrote:Oren wrote:timvw wrote:I prefer the DATE or DATETIME type... Simply because it covers much more dates than a unixtimestamp....
I'll second that.
and I'll third....
me too, me too.
