Btukfyl

Postgresql... How to find current timezone EST or EDT, I am doing it But expecting a simple solution for all timezone.,

-

Exact requirements: if I gave ET as local timezone I need current timezone with daylight.

-
SELECT
(CASE WHEN ((CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') - CURRENT_TIMESTAMP = '-04:00:00') then 'EDT'
WHEN ((CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') - CURRENT_TIMESTAMP = '-05:00:00') then 'EST'

ELSE ('OTH') END)

-

FOLLOWING IS THE FUNCTION I AM USING FOR THIS PURPOSE:

-

CREATE OR REPLACE FUNCTION public.time_zone_tz_abbriv (
abbriv text
)
RETURNS text AS
$body$
SELECT
CASE WHEN 'ET' = $1 THEN (CASE WHEN ((CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') - CURRENT_TIMESTAMP) = '04:00:00' then 'EDT'
WHEN (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')) = '05:00:00' then 'EST' END)
WHEN 'MT' = $1 THEN (CASE WHEN ((CURRENT_TIMESTAMP AT TIME ZONE 'America/Edmonton') - CURRENT_TIMESTAMP) = '06:00:00' then 'MDT'
WHEN (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP AT TIME ZONE 'America/Edmonton')) = '07:00:00' then 'MST' END)
WHEN 'PT' = $1 THEN (CASE WHEN ((CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles') - CURRENT_TIMESTAMP) = '07:00:00' then 'PDT'
WHEN (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles')) = '08:00:00' then 'PST' END)
WHEN 'CT' = $1 THEN (CASE WHEN ((CURRENT_TIMESTAMP AT TIME ZONE 'America/Mexico_City') - CURRENT_TIMESTAMP) = '05:00:00' then 'CDT'
WHEN (CURRENT_TIMESTAMP - (CURRENT_TIMESTAMP AT TIME ZONE 'America/Mexico_City')) = '06:00:00' then 'CST' END)
END
$body$
LANGUAGE 'sql'
IMMUTABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER

COST 100;

-

FOLLOWING ARE USAGE

SELECT TIME_ZONE_TZ_ABBRIV('ET');
Ans: EST or EDT
SELECT TIME_ZONE_TZ_ABBRIV('PT');
Ans: PST or PDT
SELECT TIME_ZONE_TZ_ABBRIV('MT');
Ans: MST or MDT
SELECT TIME_ZONE_TZ_ABBRIV('CT');
Ans: CST or CDT

As long as your requirement is really the time zone EDT/EST, MDT/MST and such for the current timestamp, you can use pg_timezone_names.

It is supposed to always return the correct abbreviation based on your system clock.

Example:

SELECT *

FROM pg_timezone_names

WHERE Name IN ('America/Edmonton','America/New_York')

Also, all the abbreviations are in pg_timezone_abbrevs regardless of the date

Example:

SELECT *

from pg_timezone_abbrevs

WHERE Abbrev in ('EST','EDT')