Best way to store a date and time in a database?

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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Best way to store a date and time in a database?

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

If you did use YYYY-MM-DD HH:MM:SS, how would you select from the database between two dates?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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... 8)
Last edited by Luke on Tue Nov 07, 2006 10:06 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

remember quotes around those otherwise it's math to the database ;)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I prefer the DATE or DATETIME type... Simply because it covers much more dates than a unixtimestamp....
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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....
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. :)
Post Reply