Date and Time

[PhilAtWysdom]

Hi, firstly, thanks for Jennifer, it's a nice ORM and we enjoy using it!

One way in which it appears to differ from Granite (for example) is the handling of TIMESTAMP (WITHOUT TIME ZONE) columns (at least in Postgres).

When it comes to this type of column, one can't safely assume that the value in the database should be an absolute UTC time stamp.

There is a case where it is desirable to be able to store a local time at face value into a TIMESTAMP field and to then be able to have that time load back directly at a specified local time. As things are now, it's reasonable to make a case for converting to UTC on save by default, on the basis of consistency - but some applications are built to support users from multiple time zones who are inputting events, that are to occur at a future date, in their local zone. It should be fine in those cases, to be able to store just the wall clock time as-is, without shifting to UTC before persisting.

It appears that Jennifer does convert (changing the wall clock time) to UTC, undoing any local time offset, before persisting the data for this column type, and performs the reverse, reapplying the offset when loading the date.

I'd like to suggest that (for TIMESTAMP WITHOUT TIME ZONE) only, Jennifer persist the Time object at face value without converting to UTC before storage. If the system time is already UTC, it will end up UTC as normal, but when the time is not UTC, it would store the wall clock time at the zone specified by Time::Location.local

Then, it would be nice to be able to specify at which zone the time is supposed to be interpreted when fetching (by specifying the timezone to use at runtime for dynamic cases where each application user may have their own zone.

[imdrasil]

Hi, I'm glad that you like Jennifer. Time zone related questions are always pretty complicated. I will take a look what can be done to provide the post flexible interface that can be achieved

[PhilAtWysdom]

Thanks, yes, time zones can be pretty complex, I always like to break them into two distinct types; Things that happened at a fixed point in time (e.g. created_at, updated_at, etc) - all UTC, and things that are due to happen at some known local wall clock time in the future (which we don't know the UTC timestamp of yet because the government might change the zone rules before we get there)

[didactic-drunk]

but some applications are built to support users from multiple time zones who are inputting events, that are to occur at a future date, in their local zone.

If you have users in multiple time zones inputting events then either:

1. You store the event as timestamp in the users timezone
2. Use timestamp without timezone and convert to the users time zone in code.

You can't have the db automatically apply the user's time zone to a timestamp without time zone because there are multiple users. How would the db or jennifer know which timezone to apply?

Consider SELECT * FROM events where each user has a different timezone.
How can jennifer handle autoconversion?

[PhilAtWysdom]

I wouldn't expect Jennifer to handle autoconversion. The way I see it is that Jennifer should probably not try to autoconvert dates with Time.local value to_utc before persisting them and back again when loading them in the case of a regular TIMESTAMP (no zone) column because by doing so, it assumes that you wanted to offset that time. I believe that each application should keep track of the user's zone info and the database itself should just store the time at face value without keeping the zone or performing any kind of translation to local time. However, if you could specify in the context of the query which zone a TIMESTAMP column should be treated as somehow (and I mean at runtime), then the time object returned would be in the correct zone. One example is an application that we have which manages future events. We store those future event times in a TIMESTAMP WITHOUT TIME ZONE postgres column and we want the local time value in there (because we don't know if the TZ rules will change for our zone before that event occurs) - then once the event time has passed in our local zone, we can retrospectively calculate actual time differences that occurred, etc. At the moment, when we pass the ORM a Time.local object, Jennifer converts that to UTC and stores it. It also attempts to convert back again on load. To actually store local time in the database we have to do Time.local.to_local_in Time::Location::UTC to lie and pretend it's UTC so that it gets left alone. Then, when we load data back again, Jennifer offsets the time back to our local zone, knocking it off by one hour, so to counteract that at the moment, we have to do column.to_utc.to_local_in Time::Location.load("our_zone") so as to pretend it's local time again without changing the actual wall clock time. At face value, it might seem pointless doing that, but if we store the value in UTC, then how can we be sure that a future calendar event at a specific time won't get moved (for example from 9am to 8am) due to a government policy change on daylight savings that occurred after the event was originally created? (Also, this is not something correctly handled by many platforms since it can be a confusing subject). Long story short: Absolute time is different from Wall Clock time and should really be treated as a different, incompatible Time type.