datetime or date and time?

[sharkyJay]

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?

[poirot]

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.

[wildteen88]

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:
[code]date("d-m-y", $row['timestamp']);[/code]
Also you can easily compare dates too with timestamps.

[poirot]

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:

[quote]@poirot

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

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

[wildteen88]

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

[Barand]

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

[code]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'[/code]

gives no rows.

[poirot]

So I can use this, right?

[code]$date = date ('Y-m-d');

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

[Barand]

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

[code]$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'[/code]