Menu
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.
414
In PostgreSQL, you can use the AT TIME ZONE clause to convert a timestamp to another timezone. timestamp with time zone AT TIME ZONE zone timestamp without time zone AT TIME ZONE zone time with time zone AT TIME ZONE zone Where zone is the timezone that you want the value on the left to be converted to.
PostgreSQL assumes your local time zone for any type containing only date or time. All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client. PostgreSQL allows you to specify time zones in three different forms:
PostgreSQL supports the full set of SQL date and time types, shown in Table 8-9 . The operations available on these data types are described in Section 9.9. Table 8-9. Date/Time Types Note: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior.
CURRENT_TIMESTAMP returns the current date, time, and time zone offset (using the date, time, and time zone of the machine on which PostgreSQL is running). This is returned as a value in the 'YYYY-MM-DD hh:mm:ss.nnnnnn+/-tz' format. In this format: YYYY is a 4-digit year. MM is a 2-digit month. DD is a 2-digit day of the month.
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.
121
"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).
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
