Today I Learned

hashrocket A Hashrocket project

Time Zone offsets in the DB

When you need a timezone offset in the database use

select utc_offset from pg_timezone_names where name = 'Asia/Hong_Kong';

But in this case utc_offset is an interval and maybe you want an integer

select '1 hour'::interval::integer;   -- << THROWS ERROR

Throws an error cannot cast type interval to integer

But you can extract the hour from an interval

select extract(hour from utc_offset) from pg_timezone_names where name = 'Asia/Hong_Kong';

-[ RECORD 1 ]
date_part | 8

This operation can be unexpectedly expensive to run (100ms) so be careful where you run it or maybe put this rarely changing information into a materialized view.

See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.