How to format "time with time zone" in Postgres?

Question

I have a database field of type time with time zone. How can I query this field in EST5EDT time zone with the output format hh:mm:ss?

All I can find is using multiple calls to EXTRACT:

SELECT EXTRACT(HOUR FROM TIME WITH TIME ZONE '20:38:40-07' AT TIME ZONE 'EST5EDT');

[Edit:]

To be clear, this query:

SELECT TIME WITH TIME ZONE '20:38:40.001-07' AT TIME ZONE 'EST5EDT';

Returns "22:38:40.001" but I just want "22:38:40" as the output.

Answer 1

You can simply use CAST and CAST to the time type:

SELECT CAST(now() AS TIME)

(Of course, the result will have a different data type)

Answer 2

Use the TO_CHAR() function:

SELECT TO_CHAR(date '2001-09-28' +time, 'HH24:MI:SS')

Seeing that TO_CHAR only accepts the timestamp datatype, you need to concatentate an arbitrary date value to your time value.

Answer 3

"time with time zone" is basically a bogus datatype, and as such isn't really supported in a lot of cases.

How do you define "time with time zone"? Time zones are dependent on dates, which aren't included in the datatype, and thus it's not fully defined. (yes, it's mandatory by the SQL standard, but that doesn't make it sane)

You're probably best off first fixing what datatype you're using. Either use timestamp with time zone, or use time (without time zone).