Date vs. Datetime

I think it's related to http://dev.mysql.com/doc/refman/5.0/en/datetime.html , except that for whatever reason cck date is stored as MySQL varchar(20) rather than MySQL date (and cck datetime is stored as MySQL datetime).

There is a description that used to show up until CCK's process for adding new fields got changed. The dates are stored differently in the database -- the date field is stored as an ISO date in a varchar field and the datetime field is stored in a regular datetime field. The ISO date is useful if you want to store incomplete dates, like year and month only. The datetime field is better when you are storing complete dates.

I haven't had time to update the docs.

Use of datetime fields should be actively discouraged in favor of datestamps. They take up less storage space in the database, they're less computationally intensive to handle and perform algebra upon (such as accounting for time zones), and, of course, they're far more portable should you ever decide to move to a database system which does not have an equivalent to a datetime field.

I disagree. Datetime fields are in the right format to do datetime computations in the database (like extracting a year or formating the value), unix timestamps have to be converted to date fields first using FROM_UNIXTIME in MYSQL and other things in other databases. The conversion has a cost, too, and is different in every database. And when it comes to timezones -- databases have built-in ways to do timezone conversions on dates stored in native datetime format, when you store them in other formats you end up trying to do conversion using timezone offsets, something that is prone to error when you take into account daylight savings time.

If you want dates, I think it makes sense to use the native date storage method.

Unix timestamps have to be converted to… what? Why would you have to convert a timestamp? Just select the timestamp as an integer from the database and pass it along to PHP as such, because that's how PHP is going to deal with it anyway. Does your module actually convert back and forth like that? I'm going to peek at the code…

EDIT: Oh dear, you are. So I'm assuming you must be strtotime()ing it later in order to get it to be an actual timestamp, right? That's two unnecessary and rather expensive conversions, don't you think? Consider selecting both timestamps and datetimes as ints instead. That way, the SQL server will do the timestamp-to-int conversion, which should be faster than strototime() because the SQL server will already know how the incoming data will be formatted.

You're assuming that the only thing we do with dates is pull them out of the database and display them in the node using php. To do date handling in Views we have to do conversions in the SQL because that's how Views works. To pull dates out for a calendar which are stored in UTC but will be displayed in the calendar in other timezones requires timezone conversion in the SQL so we can tell which dates to select. To see if the 'MONTH' of a date fits a Views query requires converting the date to the local timezone and then extracting the month.

There is already a huge long thread on g.d.o about the advantages and disadvantages of integers and other ways that dates can be stored. And you can Google around about the topic and you will also find lots of disagreement about what storage method is best and which is best for event/calendaring systems. It is not black and white. Timestamps are not always better and not always the solution. The way things are done in the date module comes from several years of trying to find the best way to create a system that will do all the things that this system is trying to do, and integrating into Views is a huge one of those things.

I don't convert the dates back into timestamps because we don't need them to be timestamps -- we're using the PHP 5 date functions that handle dates in any format with no need to make them into timestamps in order to work with and format them.

Don't come here and 'peek' at this code which has involved thousands of hours of work and declare that it is all done wrong because it's not immediately obvious to you why it works the way it does.

There is already a huge long thread on g.d.o about the advantages and disadvantages of integers and other ways that dates can be stored.

Could you link to this thread, please?

Probably this one which was the first one that came up when I searched on "date timestamp".

Michelle

…Wow, okay. One of the fundamentals I learned when I first started slinging PHP was Thou Shalt Use Timestamps for Everything. Seeing so many people that disagree is surprising… and a little frightening. Have I been wrong all this time?

Now I can see some arguments in favor, but they seem to be to be edge cases. Of course there are times when one must use a date between 1901 (how come so few people in that thread were aware that timestamps can be represented by negative integers?) and 2038 and 64-bit integers aren't available to do so, but the most common use for timestamps in Drupal -- representing the date and time a node was created or edited, or storing times of upcoming events in the near future as a calendar-ish module would do -- dates beyond this range are unnecessary. So I think it's fine if these edge cases make use of custom database fields and other tricks, but I think I'll continue to save bits and cycles by using timestamps for my more pedestrian time storage uses.

The day of the 32-bit integer is numbered… by y2k38, who knows how many bits we'll be using? I don't predict a problem.

If you like timestamps, you can use them, that's the reason there are different options for the type of date.

I still disagree that the reasons for using other types of date storage are 'edge cases' -- I have projects that use them all the time. We all know about negative timestamps -- but they don't work on Windows installations. And FROM_UNIXTIME doesn't work for postgresql (or any database other than MYSQL), so some of the reasons are for cross-database and cross-system support. And another problem with timestamps is the difficulty of accurately doing timezone conversions when daylight savings time is involved. If all you're doing with dates is storing them and displaying them in nodes and you're not using multiple timezones, storing them in timestamps probably works fine. But the problems of getting the right results from complex Views queries that involve timestamps has been a huge problem that Earl (and I) have spent way too much time on. And the timezone conversion issues are a nightmare I can't begin to describe.

For anyone finding this page through search, the Date documentation has been updated to explain the difference between these fields. Scroll down to the Date Fields section of this page.

Hi,
there is a philosophical / mathematical aspect about the granularity and timezone thing.

Granularity -> Intervals:
A date specified as 2011-04-26 [timezone] does not mean a specific moment, but rather a time interval that lasts 24 hours. The same for month, which specifies a time interval that lasts up to 31 x 24 hours.

Timezones -> Space-time rhomboid:
If you do not specify a timezone, it gets worse: Now the string 2011-04-26 does not specify an exact time interval, but rather a rhomboid-shaped subset of space-time): Start and end time depend on your location. (a quite pixely rhomboid, and with two added dimensions)

This also implies two different concepts of simultaneity.
1. Two events happen at the same time, if a calendar and clock in event A location shows the same display value as a calendar and clock in event B location. Example: Christmas eve in NY, vs Christmas eve in Paris.
2. Two events happen at the same time, if you could make a phone call from A to B, and see both events begin at this exact moment.

This distinction is quite important to decide whether you should use timestamps or explicit date strings.

If you want to schedule a cross-timezone phone call, then by any means, use the timestamp, The same if you want to store creation dates etc in a way that allows to compare these later.

On the other hand, things like "Easter 2011" or a "Universal Children's Day" or "Mc Donalds Fish week" (if it spans more than one timezone) are perfectly described with the space-time rhomboid.

For concerts in a local bar, it probably does not matter, because you need to travel there and accept the local timezone before this gets any relevant to you.

Finally, if the exact time is not known, then the rhomboid space-time thing serves as an approximation. Strangely shaped, but convenient for human language.

--------------------

Now, if you want to use this data to display a "list of events happening in month x", then you need to ask what kind of events?
If we use timestamps, then two site visitors in different locations might see different events in that list.
If we use date strings (and don't make them behave like timestamps), then a person in Australia will see the same events as a person in Alaska, even if "this month" has a different meaning for both of them.

----------------

Why am I saying all this?
Because I think something in this direction should go in the docs (as a sub-page if you want), and it might be useful for further discussion.
(I would wiki-add it myself, but I am not that confident yet about the stuff I just wrote)

Thank you msielski (comment #12). It was hard to find that succinct description minus the battle scars of "which is better"...

See docs pages if you want more.
http://drupal.org/node/262062