How to find current timezone EST or EDT
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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
postgresql dst edt
add a comment |
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
postgresql dst edt
add a comment |
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
postgresql dst edt
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
postgresql dst edt
postgresql dst edt
edited Nov 22 '18 at 13:45
Sivasakthi Chandrasekaran
asked Nov 22 '18 at 12:41
Sivasakthi ChandrasekaranSivasakthi Chandrasekaran
257
257
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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')
Will it change automatic on daylight savings..?
– Sivasakthi Chandrasekaran
Nov 26 '18 at 12:14
Yes: postgresql 11 on my laptop did not even need to be restarted to change the result. I do not know if it depends on a parameter in the ini file though, so if changing the date for your server is something you can do, I advise you to try.
– FXD
Nov 26 '18 at 13:29
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53431274%2fhow-to-find-current-timezone-est-or-edt%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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')
Will it change automatic on daylight savings..?
– Sivasakthi Chandrasekaran
Nov 26 '18 at 12:14
Yes: postgresql 11 on my laptop did not even need to be restarted to change the result. I do not know if it depends on a parameter in the ini file though, so if changing the date for your server is something you can do, I advise you to try.
– FXD
Nov 26 '18 at 13:29
add a comment |
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')
Will it change automatic on daylight savings..?
– Sivasakthi Chandrasekaran
Nov 26 '18 at 12:14
Yes: postgresql 11 on my laptop did not even need to be restarted to change the result. I do not know if it depends on a parameter in the ini file though, so if changing the date for your server is something you can do, I advise you to try.
– FXD
Nov 26 '18 at 13:29
add a comment |
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')
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')
edited Nov 25 '18 at 11:27
answered Nov 25 '18 at 11:20
FXDFXD
1,526128
1,526128
Will it change automatic on daylight savings..?
– Sivasakthi Chandrasekaran
Nov 26 '18 at 12:14
Yes: postgresql 11 on my laptop did not even need to be restarted to change the result. I do not know if it depends on a parameter in the ini file though, so if changing the date for your server is something you can do, I advise you to try.
– FXD
Nov 26 '18 at 13:29
add a comment |
Will it change automatic on daylight savings..?
– Sivasakthi Chandrasekaran
Nov 26 '18 at 12:14
Yes: postgresql 11 on my laptop did not even need to be restarted to change the result. I do not know if it depends on a parameter in the ini file though, so if changing the date for your server is something you can do, I advise you to try.
– FXD
Nov 26 '18 at 13:29
Will it change automatic on daylight savings..?
– Sivasakthi Chandrasekaran
Nov 26 '18 at 12:14
Will it change automatic on daylight savings..?
– Sivasakthi Chandrasekaran
Nov 26 '18 at 12:14
Yes: postgresql 11 on my laptop did not even need to be restarted to change the result. I do not know if it depends on a parameter in the ini file though, so if changing the date for your server is something you can do, I advise you to try.
– FXD
Nov 26 '18 at 13:29
Yes: postgresql 11 on my laptop did not even need to be restarted to change the result. I do not know if it depends on a parameter in the ini file though, so if changing the date for your server is something you can do, I advise you to try.
– FXD
Nov 26 '18 at 13:29
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53431274%2fhow-to-find-current-timezone-est-or-edt%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown