OraFAQ Forum: SQL & PL/SQL » Is there a more concise way to extract standard time zone than this?

Home » SQL & PL/SQL » SQL & PL/SQL » Is there a more concise way to extract standard time zone than this?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there a more concise way to extract standard time zone than this? [message #690324]

Fri, 21 March 2025 09:37

Darth Waiter
Messages: 87
Registered: October 2020

Member

Here's what I've slapped together:

select TO_CHAR(EXTRACT(TIMEZONE_HOUR from from_tz(CAST (t.LAST_ANALYZED as timestamp), '-01:00')), 'FM00') || ':' || LPAD(CAST(EXTRACT(TIMEZONE_MINUTE from from_tz(CAST (t.LAST_ANALYZED as timestamp), '-01:00')) AS VARCHAR2(3)), 2, 0) as tz
from all_tables t
where rownum < 9

Don't mind the table or column - they are simply examples that are always available.

TZ
-01:00
-01:00
-01:00
-01:00
-01:00

[Updated on: Fri, 21 March 2025 09:37]

Report message to a moderator

Re: Is there a more concise way to extract standard time zone than this? [message #690325 is a reply to message #690324]

Fri, 21 March 2025 10:25

John Watson
Messages: 8973
Registered: January 2010
Location: Global Village

Senior Member

IS this what you need:

orclz>
orclz> select to_char(systimestamp,'TZH:TZM') from dual;

TO_CHA
------
+00:00

orclz>

Report message to a moderator

Re: Is there a more concise way to extract standard time zone than this? [message #690326 is a reply to message #690325]

Fri, 21 March 2025 10:34

Darth Waiter
Messages: 87
Registered: October 2020

Member

Yes, this is perfect!

On a different topic, emails from the forum still contain invalid links:

[ <a href=3D"//www.orafaq.com/forum/?t=3Dpost&reply_to=3D690325">Reply<=
/a> ][ <a href=3D"//www.orafaq.com/forum/?t=3Dpost&reply_to=3D690325&quot=
e=3Dtrue">Quote</a> ][ <a href=3D"//www.orafaq.com/forum/?t=3Drview&goto=3D=
690325#msg_690325">View Topic/Message</a> ][ <a href=3D"//www.orafaq.com/=
forum/?t=3Drview&th=3D208739">Unsubscribe from this topic</a> ]

The 3D after `href=` and double quotes around the rest still mess them up. Colon is

& # 58 ;

&colon;

in HTML, not 3D.

[Updated on: Fri, 21 March 2025 10:36]

Report message to a moderator

Re: Is there a more concise way to extract standard time zone than this? [message #690328 is a reply to message #690326]

Fri, 21 March 2025 11:00

Michel Cadot
Messages: 68755
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I added your post in the topic you recently created in Suggestions & Feedback forum, and report it to Frank (our webmaster and site owner).

Report message to a moderator

Re: Is there a more concise way to extract standard time zone than this? [message #690329 is a reply to message #690326]

Sat, 22 March 2025 08:29

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

You want to get table last analyzed date time zone offset, right? Then use at time zone and not just TZH:TZM Compare:

SQL> select  last_analyzed,
  2          to_char(cast(last_analyzed as timestamp with time zone),'tzh:tzm') wrong_offset,
  3          to_char(cast(last_analyzed as timestamp with time zone) at time zone 'US/EASTERN','tzh:tzm') correct_offset
  4    from  dba_tables
  5    where rownum <= 4
  6  /

LAST_ANALYZED       WRONG_OFFSET CORRECT_OFFSET
------------------- ------------ --------------
03/03/2025 22:01:41 -04:00       -05:00
03/16/2021 06:35:39 -04:00       -04:00
01/26/2025 06:01:52 -04:00       -05:00
03/16/2021 06:37:52 -04:00       -04:00

SQL>

SY.

Report message to a moderator

Re: Is there a more concise way to extract standard time zone than this? [message #690330 is a reply to message #690329]

Sat, 22 March 2025 08:39

Darth Waiter
Messages: 87
Registered: October 2020

Member

What am I missing?

LAST_ANALYZED	WRONG_OFFSET	CORRECT_OFFSET
2025-03-10 22:06:05.0	-04:00	-04:00
2025-03-10 22:06:05.0	-04:00	-04:00
2025-03-10 22:00:59.0	-04:00	-04:00
2025-03-21 00:41:49.0	-04:00	-04:00

[Updated on: Sat, 22 March 2025 08:39]

Report message to a moderator

Re: Is there a more concise way to extract standard time zone than this? [message #690331 is a reply to message #690330]

Sat, 22 March 2025 08:59

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Nothig. Your dates are all after Mar 9, when we had DST 1 hour ahead, so offset for all dates after 2am Mar 9, 2025 is -04:00. Check dates before that date but after Nov 3, 2024 2 am. Correct offset should be -05:00.

SY.

Report message to a moderator

Previous Topic:	Now you see it. Now you don't.
Next Topic:	Row generator

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 25 21:50:37 CDT 2025