How do you preserve formatting of `timestamptz` when converting to text/varchar

Question

To get the current time with PostgreSQL, I have a simple query:

select now()::timestamptz

and an example output of that is

2022-06-15 23:43:41.418655 +00:00

However, when trying to work with this data in Java, this must be converted to Java's String type, which under the hood seems to cast timestamptz to text or varchar, which as you can see has different formatting:

2022-06-15 23:43:41.418655+00

(this is the same output with an explicit select now()::timestamptz::text or select now()::timestamptz::varchar; also, this output happens in a PostgreSQL command in a command line/visual console, not something weird with Java or a library itself)

Is my only option to strip off the last 3 characters and append " +00:00" through Java, or is there a way I can modify my query to produce the desired results? (also I'm not sure if the 00's are guaranteed)

Note: This project uses PostgresQL version 10.21

score 0 · Answer 1 · answered Jun 16 '22 at 00:53

0

select now()::timestamptz;

is just a noisy way of saying:

select now();

now() already returns timestamptz.

To get your desired format as text, use to_char():

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS.US TZH:TZM')

In particular the template patterns TZH and TZM.

And be aware that the output depends on the timezone setting of your current session - which happens to be UTC in your example, but that's inherently unreliable. You may want to define your time zone explicitly ...

Ignoring time zones altogether in Rails and PostgreSQL

answered Jun 16 '22 at 00:53

Erwin Brandstetter

605,456
145
1,078
1,228

Your change returns `2022-06-16 15:26:41.764913 UTCH:UTCM` – Samathingamajig Jun 16 '22 at 15:29
That issue appears to be a matter of versioning, since TZH and TZM were only added in PostgresQL version 11 – Samathingamajig Jun 16 '22 at 15:46
Goes to show the importance of dosclosing the Postgres version number in question ... – Erwin Brandstetter Jun 16 '22 at 15:58
I wasn't aware my company was using an extremely outdated version, I'll check in the future – Samathingamajig Jun 16 '22 at 16:15
Not extremely outdated, your major version is still supported. https://www.postgresql.org/support/versioning/ Upgrading to the latest version is typically beneficial, though. Would solve the issue at hand in any case. – Erwin Brandstetter Jun 16 '22 at 16:27

score 0 · Answer 2 · answered Jun 16 '22 at 16:48

Why does it matter?

Postgres can work with 2022-06-15 23:43:41.418655+00:

select '2022-06-15 23:43:41.418655+00'::timestamp; 
06/15/2022 23:43:41.418655

or

select '2022-06-15 23:43:41.418655+00'::timestamptz; 
06/15/2022 16:43:41.418655 PDT

The data is not stored formatted in Postgres anyway. The formatting you see is for input/output purposes and is determined by your locale and DateStyle settings. It can be changed. The important part is that the values are correct.

How do you preserve formatting of `timestamptz` when converting to text/varchar

2 Answers2