To_char(): Not able to get timezone offset displayed

CrateDB
1

According to the docs for to_char, a timestamp can be formatted using the following specifiers (among others): TZ and OF. However, I’m not able to get any of them printed:

cr> select to_char('2023-01-01T00:00:00+01:00'::timestamptz, 'YYYY-MM-DDTHH24:MI:SS TZ OF');
+-------------------------+
| '2022-12-31T23:00:00  ' |
+-------------------------+
| 2022-12-31T23:00:00     |
+-------------------------+
SELECT 1 row in set (0.004 sec)
cr> select to_char('2023-01-01T00:00:00+01:00', 'YYYY-MM-DDTHH24:MI:SS TZ OF');
+-------------------------+
| '2022-12-31T23:00:00  ' |
+-------------------------+
| 2022-12-31T23:00:00     |
+-------------------------+
SELECT 1 row in set (0.002 sec)
cr> select to_char(timezone('UTC', '2023-01-01T00:00:00'), 'YYYY-MM-DDTHH24:MI:SS TZ OF') as x;
+---------------------+
| x                   |
+---------------------+
| 2023-01-01T00:00:00 |
+---------------------+
SELECT 1 row in set (0.002 sec)
cr> select to_char(timezone('Europe/Berlin', '2023-01-01T00:00:00'), 'YYYY-MM-DDTHH24:MI:SS TZ OF') as x;
+---------------------+
| x                   |
+---------------------+
| 2023-01-01T01:00:00 |
+---------------------+
SELECT 1 row in set (0.002 sec)

Is this really supported, am I using the wrong syntax, or yet something else?

2

TIMESTAMP and TIMESTAMPTZ store timestamps always in UTC time and lose any timezone information. The timezone specifiers always return "" see crate/server/src/main/java/io/crate/expression/scalar/formatting/DateTimeFormatter.java at efeb10240fe14e11141b82019e2e9d381c4e3da5 · crate/crate · GitHub

3

Well so perhaps it’s worth documenting that those specifiers do nothing.
Any other way to obtain the same information?

Btw, even if timezone information is dropped when data is stored in the db, I’d expect that a bare call like select timezone('Europe/Berlin', '2023-01-01T00:00:00'), where nothing is stored or read to/from the db, should indeed have timezone information attached.

4

Well so perhaps it’s worth documenting that those specifiers do nothing.
Any other way to obtain the same information?

Yes, probably makes sense to adjust the docs.

Btw, even if timezone information is dropped when data is stored in the db , I’d expect that a bare call like select timezone('Europe/Berlin', '2023-01-01T00:00:00') , where nothing is stored or read to/from the db, should indeed have timezone information attached.

also timezone() returns a TIMESTAMP (which doesn’t hold any info about the time zone) the to_char() function would only ever take the servers or sessions set timezone (e.g. SET TIMEZONE) into consideration, however this is not (yet) supported by CrateDB.

There are open issues in the crate/crate repo to track this:

2 Likes
5

Thanks. It would be really nice to be able to return timestamps with UTC offsets in response to localized queries from users, even if it has to be done using date_format() or to_char() and not like postgres using the client timezone.

1 Like
6

Dear @homer,

apologies not following your conversation too closely, but if those two issues mentioned by @proddata would help you, please also add an upvote :+1: on them on GitHub. Thanks!

With kind regards,
Andreas.

7

I’m not sure those two issues specifically would help my use case, as they seem to be about taking into consideration the client timezone setting automatically (as opposed to having to use date_format etc.) rather than introducing explicit UTC offset support in the output format. I will upvote them though.

1 Like
8

Thank you. If you think you can come up with a better ticket to describe the gap discussed here, and if it doesn’t match the others outlined by @proddata, please feel free to create a dedicated issue at crate/crate, in order to support us in this regard. Thank you again!