[FLOW3-general] DateTime limited to Unix timestamp?

[Martin Kutschker]

Am 02.07.2010 22:51, schrieb Michael Sauter:
> On 02.07.10 20:51, Martin Kutschker wrote:
>> Am 01.07.2010 23:54, schrieb Michael Sauter:
>>>
>>> Could you explain that a bit? Which cases exist where storing timestamps (with an additional
>>> timezone) is not enough?
>>
>> With an added TZ field it's probably "enough" for many cases. But I fail to see what is so great
>> about unix timestamps.
>>
>>>> For me a storage format of yyymmddThhmmss is much more readable than a timestamp and you can get
>>>> the
>>>> parts with simple string operations.
>>>
>>> Does storage need to be readable? I think it's more important to be readable by computers, not
>>> humans.
>>
>> Of course it doesn't have to be readable per se, but it helps a lot when you have to wade through
>> raw SQL data a lot.
>>
>> When you use DATETIME or a date formatted CHAR you can do all kind of queries directly (like
>> selecting records from a given date) and intuitively. You cannot do that with unix timestamps.
>>
>> IMHO they are only handy for simple logging tables, but even then they have drawbacks.
>>
>>>> But we left out the obvious choice: an SQL date field. It has all kinds of bells and whistles (like
>>>> arithmetic) and is built for the job.
>>>
>>> AFAIK SQL databases differ on their date type handling. MySQL for example has (among others)
>>> TIMESTAMP  and DATETIME, I guess you are referring to the latter?
>>
>> Of course.
>>
>>> I think it's also important to keep an eye on non-SQL databases (I know, not yet there in FLOW3, but
>>> who knows?). Obviously the interface of interacting with a "backend" should be the same. But if you
>>> can't rely on date functions for all backends (even SQL databases differ concerning date functions),
>>> it's getting harder to have the same interface. Dealing with a relatively simple type like integer
>>> is easier.
>>
>> How the data is stored in the BE shouldn't worry you in the client code. If all you get is a
>> DateTime object why worry if it's stored as a unix timestamp or as a SQL DATETIME field?
> 
> That is more or less what I'm saying ;) The client code should be the same, no matter the BE. So
> FLOW3 needs to be sure that all functionality it offers should work with (almost) any databases. Say
> you want to use a SQL DATETIME field to store dates, because it is a good way to store dates (I
> agree, better than timestamps). But why exactly is it better? Mainly because MySQL offers you some
> functionality that makes handling/selecting much easier. Now if FLOW3 wants to support these
> operations, it needs to support it for all databases.

I think that all modern DBs have a date/time type, though funny enough in Prostgresql the type
"timestamp" is what in Mysql is a "datetime". So the SQL dialect mapping will be fun :)

> But, in the end, date strings might really be a better choice cause as you said, there are probably
> no disadvantages compared to using timestamps (but some advantages like better readability, handling
> of historic dates). You convinced me ;)

Great :)

But will this thread change anything regarding FLOW3 or v5?

Masi