Time storage and UTC default setting

[Blama]

Hi Isomorphic,

reading this thread and then the Date and Time Format and Storage docs, I was wondering if you could do some changes to the samples and clarify one doc.

1) In "Store using UTC or local timezone?" you say:

Because UTC is a fixed reference point and not a "real" timezone, DST is not relevant and all datetimes can be recorded unambiguously without exceptions or edge cases

I agree with this – and am not doing it myself, because I started way back with my project based on BuiltInDS. BuiltInDS does not have this setting and to change it in production means that you have to update all datetime fields. Could you add this row (sql.xxx.useUTCDateTimes: true or sql.useUTCDateTimes: true) to all samples' server.properties?
I think you can't change it in framework.properties, as this would affect all users unexpectedly after update, but with this line you could deliver the default you suggest to all new users.
Perhaps even add a "# Please make sure to read the Date and Time Format and Storage docs"

2) From reading the Date and Time Format and Storage docs it is not clear to me what type to use for my database column if I want to use a .ds.xml DataSourceField with type="time".
I assume it's the database's datetime type and you just use 1970-01-01 for the day, but I'm not sure. It could also be varchar2(5) or similar (for "23:59").

Best regards
Blama

[Isomorphic]

We'll consider either changing the default (with release notes pointing out the change) or changing the sample projects to add the setting.

As far as storing times, most SQL databases directly support "TIME" as a column type, although using DATETIME or TIMESTAMP will also work. We would not recommend text-based storage as that's going to make filtering more complicated and very inefficient.

[Blama]

Hi Isomorphic,

thanks.

I wasn't aware there is a time datatype, but then it's obvious what to use. For reference, TIME datatype in the PostgreSQL docs.

Best regards
Blama