Jump to content


Photo

datetime or date and time?


  • Please log in to reply
7 replies to this topic

#1 sharkyJay

sharkyJay
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 12 June 2006 - 04:50 PM

From a practical point of view is it better to store the date and time in one field or have two?

Would have time as separate make it easier to do analysis?

#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 12 June 2006 - 05:26 PM

It's easier to have a field with the date and time. You can always use date formating functions and get the time, or the date, or both.
~ D Kuang

#3 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 12 June 2006 - 05:36 PM

It is best to store the date and time as a unix timestamp in one field. Then when you get the timestamp out of the database you can use the date function to format the time stamp:
date("d-m-y", $row['timestamp']);
Also you can easily compare dates too with timestamps.

#4 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 12 June 2006 - 05:40 PM

A lot of people like UNIX timestamps, but be careful with them because they are messy when you have dates prior to January 1 1970.

It depends on what you are going to store, but quoting Barand:

@poirot

I was born in 1949 - try storing my date of birth in a UNIX timestamp.


This was when I realized UNIX timestamps were not magical solutions.
~ D Kuang

#5 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 12 June 2006 - 06:04 PM

Umm, thats a good point there poirot I always forget about unix timestamp will only work with dates prior to Jan 1 1970! But unix timestamp is okay for doings things such as logging when someone posts a message or logs in etc but not with DoB's

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 12 June 2006 - 07:09 PM

Be careful when trying to select records by date if you have both data and time elements the column

2006-06-12 05:30:00
2006-06-12 09:30:00
2006-06-12 15:30:00

$date = date ('Y-m-d');

SELECT * FROM mytable WHERE datecol = '$date'

gives no rows.

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 12 June 2006 - 07:16 PM

So I can use this, right?

$date = date ('Y-m-d');

SELECT *, DATE_FORMAT(date_col, '%Y-%m-%d') AS c_date FROM mytable WHERE c_date = '$date'

~ D Kuang

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 12 June 2006 - 07:32 PM

Or use PHP, which I often do, contrary to your belief ;-), and

$d1 = date ('Y-m-d 00:00:00');
$d2 = date ('Y-m-d 23:59:59');

SELECT * FROM mytable WHERE datecol BETWEEN '$d1' AND '$d2'

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users