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

CodePudding user response：

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

Related:

• Ignoring time zones altogether in Rails and PostgreSQL

CodePudding user response：

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.