CODEWITHSUNDEEP
sqldatabasepostgresqltimeformatting
griffin
griffin

Reputation: 3234

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

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.

Upvotes: 5

Views: 24149

Answers (3)

Magnus Hagander
Magnus Hagander

Reputation: 25098

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

Upvotes: 4

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 12

Roland Bouman
Roland Bouman

Reputation: 31961

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)

Upvotes: 0

Related Questions