MySQL: Col for Date and another one for Time, or one column for both?

[imagined]

Hey guys!

I don't have too much experience handling time so my question may seem very basic.

I'm going to start adding events to the calendar class I'm writing. It's going to get the events from a MySQL time:

• name of event
• date of event
• time of event
• description of event
• duration of event
• category of event
• etc

My question is... should I save the TIME and DATE of the event in two different columns or would it be easier to just have the TIME and DATE of the event in ONE column?

[blazes]

1 TIMESTAMP column will do the job well.

[imagined]

so you recommend using EPOCH then?

[blazes]

Make the time 'TIMESTAMP' and the default value 'CURRENT_TIMESTAMP'.

[jsauni]

I agree with blazes, 1 timestamp column will do.

[Voetsjoeba]

Keep in mind that TIMESTAMPs have a limited range of roughly 1970 to 2038. If you need a wider range of dates, I suggest you use the DATETIME type instead (1000 to 9999). Also, remember that TIMESTAMP types will automatically perform timezone correction based on the server's current timezone while DATE(TIME) types will not. See MySQL's reference manual on DATE types for more information.

[imagined]

Originally Posted by Voetsjoeba

Keep in mind that TIMESTAMPs have a limited range of roughly 1970 to 2038. If you need a wider range of dates, I suggest you use the DATETIME type instead (1000 to 9999). Also, remember that TIMESTAMP types will automatically perform timezone correction based on the server's current timezone while DATE(TIME) types will not. See MySQL's reference manual on DATE types for more information.

I think TIMESTAMP may do, the class is intended for an appointments calendar so as long as its the near future it's fine

[imagined]

Originally Posted by blazes

Make the time 'TIMESTAMP' and the default value 'CURRENT_TIMESTAMP'.

Ok then, so let's say I want to input an appointment for November 25, 2009 at 11:00 am

You're saying I should use TIMESTAMP, so then I would input into the database: strtotime('2009-11-25 11:00:00')

Why would it be better to input the TIMESTAMP(1259175600) instead of the actual date and time(2009-11-25 11:00:00)???

I'm sorry the question may seem simple, but I haven't handled time extensively.

[blazes]

Timestamps are a lot easier to work with. If you have 2009-11-25 11:00:00, but want to display "November 25th, 2009" it's a lot more work than if you used a timestamp because otherwise you have to convert 2009-11-25 11:00:00 to a timestamp, then do the editing.

[ajcates]

And remember timestamps can be longer then 1970 - 2038 with an extra column in your database.

[simplistik]

Originally Posted by blazes

Timestamps are a lot easier to work with. If you have 2009-11-25 11:00:00, but want to display "November 25th, 2009" it's a lot more work than if you used a timestamp because otherwise you have to convert 2009-11-25 11:00:00 to a timestamp, then do the editing.

Not entirely true, that used to be the case

PHP Code:

$datetime = new DateTime('2009-11-25 11:00:00');
echo $datetime->format('F dS, Y'); 


November 25th, 2009

PHP Code:

echo date('F dS, Y', 1259146800); 


November 25th, 2009

http://www.php.net/manual/en/class.datetime.php

In this example a DATETIME is far superior to TIMESTAMP do to it's much larger date range. Also easier to manipulate the date compared to trying to manipulate a timestamp:

PHP Code:

date_add($datetime, new DateInterval('P10D'));
echo $datetime->format('F dS, Y'); 


December 05th, 2009

as well as it's far easier to read the raw data from the DB. Granted you have to have PHP 5.3 or greater, but there's no reason you shouldn't be able to get that on your server, and most good hosts have it already. At the very least create some sort of function that can imitate the native datetime class, if needed/wanted.

[imagined]

I was practicing with the new DateTime PHP class months ago, so now I will need to review it again.

Need some more practice with OOP. The Calendar class I'm writing is not totally OO, but it's heading that way.

Thanks a lot for your comments guys!