Comments (14)
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 returnsTIMESTAMP_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';
name | value | description | input_type | scope |
---|---|---|---|---|
TimeZone | America/Los_Angeles | The current time zone | VARCHAR | GLOBAL |
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';
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.
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;
y |
---|
2015-01-01 |
from duckdb.
Think this got sorted?
from duckdb.
Related Issues (20)
- ARRAY_VALUE(0.5, 0.4, 0.1) --> Unimplemented type "DECIMAL(2,1)[3]"
- Error parsing dataframe from variable name in 0.10.3 HOT 1
- C API: Writing (large) list with Appender fails HOT 7
- cannot define a macro inside a cursor - Cross catalog dependencies are not supported. HOT 5
- Why is the db.file size still large after deleting data or dropping tables? HOT 19
- Inconsistent/ unexpected error when using strptime() in combination with format-list option HOT 1
- `get_table_names` erroring on query using `generate_series` in CTE, but executes it without issue
- Aggregate Regression function SLOPE and INTERCEPT seem to produce wrong results HOT 8
- read_csv invalidates DuckDB instance HOT 8
- Polars example raises InvalidInputException error HOT 2
- Parquet hive partition write error with OVERWRITE_OR_IGNORE - Deleting other partition data HOT 3
- read_parquet defaults hive_partitioning to auto, not false HOT 2
- ATTACH does not show new table in attached DB on DBeaver HOT 2
- Segmentation fault during PIVOTING table (CLI) using struct field HOT 3
- Entry type not supported in PhysicalCopyDatabase HOT 3
- BM25 matching scores seems to be invalid HOT 1
- read_csv with with store_rejects=true does not work as intented with constraints HOT 4
- DuckDB goes into fatal mode when it fails to write parquet data in a mounted file system HOT 2
- Issue with Python duckdb.read_csv not working with 0.10.2+ HOT 10
- Treatment of interval type in Python HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from duckdb.