# To\_char(): Not able to get timezone offset displayed **URL:** \ **Category:** CrateDB\ **Created:** [January 20, 2024, 3:13pm UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698 "2024-01-20T15:13:28Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![homer](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@homer](https://community.cratedb.com/u/homer)\ **Post date:** [January 20, 2024, 3:13pm UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698/1 "2024-01-20T15:13:28Z")
According to [the docs for `to_char`](https://cratedb.com/docs/crate/reference/en/5.5/general/builtins/scalar-functions.html#to-char-expression-format-string), 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: ```auto 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? ---
**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\ **Post date:** [January 21, 2024, 7:27pm UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698/2 "2024-01-21T19:27:42Z")
`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](https://github.com/crate/crate/blob/efeb10240fe14e11141b82019e2e9d381c4e3da5/server/src/main/java/io/crate/expression/scalar/formatting/DateTimeFormatter.java#L417) ---
**Author:** ![homer](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@homer](https://community.cratedb.com/u/homer)\ **Post date:** [January 21, 2024, 9:49pm UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698/3 "2024-01-21T21:49:14Z")
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. ---
**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\ **Post date:** [January 22, 2024, 9:53am UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698/4 "2024-01-22T09:53:02Z")
> 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: > > > How to set default timezone? > SELECT DATE\_FORMAT('%Y-%m-%d %H:%i:%s',CURRENT\_TIM…ESTAMP); > query result slower 8 hours than os time! > How to set the default value in yml or startup shell scrip. > thanks. > > ref:solr can set in > tomcat > set JAVA\_OPTS=%JAVA\_OPTS% -Duser.timezone=Asia/Shanghai > or jetty > \# By default the start script uses UTC; override the timezone if needed > SOLR\_TIMEZONE="Asia/Shanghai" > > > \*\*Use case\*\*: > > Postgres allows you to set a per session time zone, which is th…en revealed by \`show time zone\` (also \`show timezone\`). In CrateDB the system time zone is always UTC, no matter where it is run, as documented \[here\](https://crate.io/docs/crate/reference/en/latest/general/ddl/data-types.html#date-time-types): > > \`\`\` > Internally, timestamp values are mapped to the UTC milliseconds since 1970-01-01T00:00:00Z stored as bigint. > \`\`\` > > \*\*Feature description\*\*: > > Enhance \`SHOW\` so that it supports \`SHOW TIME ZONE\`, to show UTC. > > This way it will be made explicit in its own section of the documentation, enforcing the notion that under the hood all of it is UTC. ---
**Author:** ![homer](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@homer](https://community.cratedb.com/u/homer)\ **Post date:** [January 22, 2024, 2:19pm UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698/5 "2024-01-22T14:19:54Z")
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. ---
**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\ **Post date:** [January 22, 2024, 2:26pm UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698/6 "2024-01-22T14:26:28Z")
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 👍 on them on GitHub. Thanks! With kind regards, Andreas. ---
**Author:** ![homer](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@homer](https://community.cratedb.com/u/homer)\ **Post date:** [January 22, 2024, 2:31pm UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698/7 "2024-01-22T14:31:21Z")
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. ---
**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\ **Post date:** [January 22, 2024, 2:35pm UTC](https://community.cratedb.com/t/to-char-not-able-to-get-timezone-offset-displayed/1698/8 "2024-01-22T14:35:46Z")
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!