#22 Establish policy for timestamps in the DB

[Oliver Gorwits]

18:05 < hotdogs> oliver: so I looked into the PG timestamp thing. The default for timestamp is without timezone, so any dates put in are stored verbatim. ND2 uses epoch time mostly (I found two calls to localtime that are for job times that can be fixed), so the queries get epoch time back out

18:09 < hotdogs> there's two ways to make it come back out in a timezone. To make the DB do it, set a timezone in the DB/on the DB session and change the selects to convert to the timestamp with timezone type. For example, "select time_recent from node;" gets UTC times. "select time_recent at time zone 'UTC' from node;" will convert it to localtime in the DB

18:09 < hotdogs> alternatively, there's a DBIx::Class::InflateColumn::DateTime class that could do the timezone change in DBIC instead

[Oliver Gorwits]

On 2014-03-14 14:33, David Koski wrote:
I have a very strange ongoing problem. A while ago I noticed that
some of my timestamps in the database where 5 hours earlier but some
of the timestamps were correct (node_ip.time_first/node_ip.time_last
was fine, but node.time_first, node.time_recent, node.time_last were
all off).

So I went in to postgresql.conf and removed all my timezone settings,
and the times reversed. With the ones being off no fine, and the
ones that were fine is off).

So obviously this is a table issue... I checked all the table
definitions and they all say "timestamp without timezone" and default
to "now()".

So what am I missing? I don't see how the database could be the
issue. The dates continue to go in the database off depending on the
column, but they are all set to "without timezone"..

[Brian De Wolf]

I did some prototyping on applying the timezone in the DBIC layer. For example, to make it work for the device details page (more or less):

Modify DB/Result/Device.pm to add near the top

__PACKAGE__->load_components(qw/InflateColumn::DateTime/);

and add to all of the timestamp/time columns

timezone => "UTC"

so that the DateTime inflation assumes the DB time is in UTC. After this is done, any calls for one of the timestamps (e.g. ->last_discover) will return a DateTime object instead of a scalar from the DB. To apply a timezone, simply manipulate the DateTime object to set the time zone

$row->last_discover->set_time_zone("America/Los_Angeles")

and the next time it's used it will be adjusted into that timezone.

Unfortunately, this doesn't really mesh well with the structure of ND2. ND2 uses lots of synthesized columns for timestamps and I'm not sure there's a clean way to adjust all of them. With my current understanding, it'd cause a bunch of boilerplate that's needed any time a new time column comes about, along with other code to adjust the timezone on it. This would also incur the overhead of DateTime objects when the user wants the times in UTC.

To summarize, on the data's path to the user, there's still a few options left. Here's the path with points marked:

1. DB -> 2. DBIC -> 3. ND::Web -> 4. browser

1. DB times should always be UTC, can't do it there.
2. DBIC can't cleanly do this, and it's more of a presentation thing anyway.
3. ND::Web could do a pass over the columns before it sends them to TT to be sent to the user.
4. Browser JS could adjust the times using the user's timezone (no input required!)

So #1 and #2 don't look so good, I'll try poking at #3 and #4 now.

[Oliver Gorwits]

I think we might need to focus on getting timestamps fixed on the server. This is because eventually I'd like all our web handlers to return JSON data structures (or at least to expose that data). This means we need to return formatted dates already at that point, rather than waiting.

However the user's timezone should still be passed in HTTP headers to the webserver, so all web handlers should be able to do formatting based on that.

[Oliver Gorwits]

Ticket moved from /p/netdisco/netdisco2/96/