Why is 'UTC-4" timezone in Postgres leads to 04, and 'UTC 4' leads to -04?-CodePudding

To me, it seems more intuitive to have 'UTC-4' associated with '-04' and vice versa.

I attached an image here of what I get when trying to select the current time in the corresponding timezone.

CodePudding user response：

Per the link in the comment from @a_horse_with_no_name, UTC-4/UTC 4 are POSIX style time zone designations and as such the direction from UTC is reversed. In other words is going west from Greenwich and - is going east. Your choices are:

Recognize that and reverse your signs as needed.
Use a known timezone say America/Porto_Velho(-04) or Asia/Baku( 04). Though this offset may change if DST rules change for that timezone.
Do something like below. I am currently in PST in USA:

select current_time;
    current_time    
--------------------
 09:05:28.464408-08

 select (split_part((current_time at time zone 'UTC' - interval '4 hours')::varchar, ' ', 1) || '-04')::timetz;
       timetz       
--------------------
 13:05:39.056446-04

select (split_part((current_time at time zone 'UTC'   interval '4 hours')::varchar, ' ', 1) || ' 04')::timetz;
       timetz       
--------------------
 21:05:50.624686 04