`DATE_TRUNC` returns wrong results for `TIMESTAMPTZ` about duckdb HOT 14 CLOSED

What time zone are you running the queries in?

from duckdb.

Bangkok/Thailand - UTC+7
But why would that matter for date trunc please?

from duckdb.

Also, DATE_TRUNC always returns TIMESTAMP_TZ - this matches the behaviour of Postgres:

hawkfish=# set timezone='US/Pacific';
SET
hawkfish=# select date_trunc('year', '2015-05-08T23:39:20.123-07:00'::TIMESTAMPTZ);
       date_trunc       
------------------------
 2015-01-01 00:00:00-08

from duckdb.

Bangkok/Thailand - UTC+7 But why would that matter for date trunc please?

I don't know, but it's part of the environment that need for reproducing TZ issues. (I've even had issues where the calendar was not Gregorian...)

from duckdb.

Also, DATE_TRUNC always returns TIMESTAMP_TZ - this matches the behaviour of Postgres:

SELECT TIMESTAMP '2015-05-08T23:39:20.123-07:00' AS "DATE1",
       typeof(DATE_TRUNC('YEAR', "DATE1")) AS "TRUNCATED TO YEAR",
       typeof(DATE_TRUNC('MONTH', "DATE1")) AS "TRUNCATED TO MONTH",
       typeof(DATE_TRUNC('DAY', "DATE1")) AS "TRUNCATED TO DAY";

/*
┌───────────────────┬───────────────────┬────────────────────┬──────────────────┐
│ DATE1             │ TRUNCATED TO YEAR │ TRUNCATED TO MONTH │ TRUNCATED TO DAY │
├───────────────────┼───────────────────┼────────────────────┼──────────────────┤
│ 09/05/2015, 06:39 │ DATE              │ DATE               │ DATE             │
└───────────────────┴───────────────────┴────────────────────┴──────────────────┘
*/


But not for TIMESTAMP?

from duckdb.

Do you mean Asia/Bangkok?

D from duckdb_settings() where name = 'TimeZone';

from duckdb.

Do you mean Asia/Bangkok?

D from duckdb_settings() where name = 'TimeZone';

name value description input_type scope
TimeZone America/Los_Angeles The current time zone VARCHAR GLOBAL

Yes, sorry, I was on a call in parallel.
Thank you for helping, I appreciate!

from duckdb.

Ah it looks like we have tweaked the binder for TIMESTAMP and this does not match PG. I think the reason we didn't is that with TSTZ truncating to a DATE depends on the TZ, which could cause problems (if the date was stored and then cast back to TSTZ in a different TZ there would be data corruption).

from duckdb.

I can't reproduce your problem on 0.10.2, but the display of your values is a bit concerning. 2014-12-31T17:00Z is not what we usually display? It looks like you may not have ICU loaded?

D from duckdb_extensions() where extension_name ='icu';

from duckdb.

My output is from out own JDBC Database browser, not from DuckDB2 directly.
I use it for simplified ASCII copy'n paste.

ICU is available:

┌────────────────┬────────┬───────────┬──────────────┬──────────────────────────────────────────────────────────────────┬─────────┬───────────────────┐
│ extension_name │ loaded │ installed │ install_path │ description                                                      │ aliases │ extension_version │
├────────────────┼────────┼───────────┼──────────────┼──────────────────────────────────────────────────────────────────┼─────────┼───────────────────┤
│ icu            │ true   │ true      │ (BUILT-IN)   │ Adds support for time zones and collations using the ICU library │ []      │                   │
└────────────────┴────────┴───────────┴──────────────┴──────────────────────────────────────────────────────────────────┴─────────┴───────────────────┘

from duckdb.

My output is from out own JDBC Database browser, not from DuckDB2 directly. I use it for simplified ASCII copy'n paste.

Ah that's the confusion then. The results you are getting are the timestamps truncated to the start of the year in your time zone, but displayed in UTC.

from duckdb.

I am honestly confused here. In my simple world, a DATE TRUNC would return a DATE which is not sensitive to a Time Zone because Date has no time. So when applying DATE TRUNC on a TIMESTAMPTZ I would have expected 1) translation to UTC TIMESTAMP and then truncating the time.

from duckdb.

Sadly we do not live in a simple world where time is concerned!

But dates are sensitive to time zone: A date is a range of instants. But to get the behaviour you want, just add a cast to DATE:

D select date_trunc('year', '2015-05-08T23:39:20.123-07:00'::TIMESTAMPTZ)::DATE y;

from duckdb.

Think this got sorted?

from duckdb.