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?
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.
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:
