Duckdb: timestamptz?

[r2evans]

Is there a specific reason that TZ support is not provided?

I might be missing something, but in my experience, (1) it can be informative to have the TZ information, regardless of the ability to compensate for time zone differences; (2) too many programmers either don't know how to correct for TZ correct -- especially with DST -- or they don't even try due to ignorance or laziness. Many of the problems I've had (with client databases) have been resolved by identifying code that forgot how to do things like this.

I recognize sqlite's DATETIME is also without TZ, but that doesn't make it the best way to go.

I suggest a way forward:

• add support TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE
• TIMESTAMP is just an alias to "without"

[Mytherin]

There is no reason besides we haven't gotten to it yet :) I think following PostgreSQL's implementation for timestamps with timezones is indeed a good idea. I have no objections to it.

[r2evans]

I'm all good for postgres, but my recommendation was based on the 2011 ANSI SQL "draft" (I don't have the official copy, just what's available for free download). Frankly, there are so many dialects out there that it doesn't buy a lot (other than pedagogy) to stick with ANSI or any one particular DBMS. If you have a systemic parallel with any one particular DBMS, then sticking with that dialect makes sense to me.

[Mytherin]

We don't have a direct parallel, but we mostly follow either what SQLite or what Postgres does. We tend to go with Postgres when it comes to features, and SQLite when it comes to leniency. Postgres is easiest for us to follow because we are using their parser, after all :)

I have taken a look at it and I think what Postgres does is very close to what you describe, and since Postgres tends to follow the SQL standard, I think it makes sense to follow them here.

[hawkfish]

I have grave concerns about using PG or the standard for TZ support. The last time I tried to use TZ support in PG it made no sense. The qualification kept disappearing and reappearing - and as far as I could tell this was expected behaviour. it made it impossible to add it to Tableau in a comprehensible manner.

[r2evans]

@hawkfish, interesting! While my use of postgres is not daily, I do use it regularly enough and haven't seen any of that behavior. Can you provide a small reprex that always or at least often produces that erroneous behavior? (I'm not trying to submit a bug report to postgres -- though that might be good -- I'm trying do route out your concerns to see if it is something that is avoidable or something that can otherwise be mitigated.)

[hawkfish]

Sadly I can't - it was several years ago. I don't have it installed on my laptop, but IIRC you start casting timestamp columns and looking at the types.

[r2evans]

Using postgres:11, I can do this:

psql (11.5 (Debian 11.5-3.pgdg90+1))
Type "help" for help.

postgres=# select current_timestamp;
       current_timestamp
-------------------------------
 2020-04-16 23:54:18.031456+00
(1 row)

and have been comfortable with that output for a while. (current_timestamp returns timestamp with time zone according to https://www.postgresql.org/docs/11/functions-datetime.html, table 9.30.)

I've not run into a situation where the +00 varies in format or accuracy (only when the importing process did it incorrectly). (I have not tested with non-hour-aligned time zones, but allegedly it will find them by their olson names or other unambiguous zone name, so the 2-digit hour on the output is likely not a limiting factor.)

Granted, I believe that PG always stores in UTC, so I do not expect to see something other than +00 from queries. The important component was that the inserting application inserted what it though the appropriate time zone is (within pg's parsing expectations).

It does have at time zone, so that works as well.

postgres=# select current_timestamp at time zone 'Canada/Newfoundland';
          timezone
----------------------------
 2020-04-16 21:40:04.973951
(1 row)

postgres=# select current_timestamp ; -- roughly a few seconds later, showing +30min offset from hourly
       current_timestamp
-------------------------------
 2020-04-17 00:10:10.006878+00
(1 row)

[hawkfish]

What I meant by the admittedly rather vague "disappearing and reappearing" was that a casting pipeline would alternately remove and add the TZ qualification, which made it pretty much useless for automated query generation where the generator might wrap calculations in casts for safety. This was v8, though, and it may have been a long fixed bug.

[hawkfish]

I think the big question with TZ support is: "What sort of analytic tasks are TZ queries supposed to enable?" For the most part, TZ support is a presentation layer issue, but knowing what the wall time was in the locale the record came from can be important (e.g., "Did the customer buy this product in the morning?") In that sense a TZ specification is actually a _calendar_ specification and the analytic queries can be phrased in terms of that calendar and its associated temporal bins and hierarchies. (By calendar here I mean a binning system for time.)

[r2evans]

I think most of my anecdotes on why I feel I "need" TZ in a field are based on my mistakes and/or those of other programmers. Many "data-gathering sessions" I work with occur in various parts of the world, and for redundancy the feed processing can be done at various locations (in differing timezones). In a perfect world, all programmers would use proven libraries and always insert the correct TZ, but typically something somewhere uses local time, and the logs or data or whatever are now difficult to gather and correlate.

I certainly get the point (from you, from krlmlr, and perhaps Mytherin) that many feel that the perceived need for TZ in the database is borne from bad programming. A different way to look at it is to enforce a "proven library" to do the conversion, one that is common to all programmers: the database.

[hawkfish]

I don't think that there is _no_ need for it, but that as you say it is often just a data cleaning/import problem, and once the data is imported correctly, there are no remaining analytic tasks that require the original time zone.

Still, I think there are analytic tasks that could use time zones, and the ones that come to mind are row level tasks (like record-level time of day queries) and global tasks (like sales reporting when a company is in a particular time zone). So if we add TZ support, we should have these kinds of tasks in mind, both for performance and user comprehensibility.