Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » SQL & PL/SQL » SQL & PL/SQL » DayLightSaving and TimeZone Conversion (merged)
DayLightSaving and TimeZone Conversion (merged) [message #314711] Thu, 17 April 2008 09:58 Go to next message
Shalini1978
Messages: 7
Registered: March 2008
Junior Member
How can I convert "local time of a country" into UTC ? My conversion should automatically take care of DayLightSaving Time if observed by that country.

Countries that I have to consider for conversion can be one of these http://userpage.chemie.fu-berlin.de/diverse/doc/ISO_3166.html

[Updated on: Thu, 17 April 2008 09:59]

Report message to a moderator
Re: DayLightSaving and TimeZone Conversion [message #314714 is a reply to message #314711 ] Thu, 17 April 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 15244
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Are those all true?
Quote:
- Do I consider myself an expert?
- Do I think the problem at hand is a difficult one?
- Is my problem strictly SQL-related?


Not an EXPERT? Post in the NEWBIES forum, NOT here and OraFAQ Forum Guide, did you read them?

Regards
Michel
Report message to a moderator
timezlrg [message #314750 is a reply to message #314711 ] Thu, 17 April 2008 12:51 Go to previous messageGo to next message
Shalini1978
Messages: 7
Registered: March 2008
Junior Member
I am using following query for timezone conversion

SELECT FROM_TZ(to_timestamp('05/01/2008 05:30','mm/dd/yyyy hh24:mi'), 'Africa/Algiers') at time zone 'UTC' as newtime from dual

Now, timezlrg.dat has following timezones for Africa/Algiers
Africa/Algiers LMT
Africa/Algiers PMT
Africa/Algiers WET
Africa/Algiers WEST
Africa/Algiers CET
Africa/Algiers CEST


How can I convert "Africa/Algiers WEST" into UTC?
Report message to a moderator
Re: timezlrg [message #314755 is a reply to message #314750 ] Thu, 17 April 2008 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 15244
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Don't start a new topic for the same question.
You found FROM_TZ function, check the other SQL time related functions.
And this is not an Expert question.
And this is not a PL/SQL question.

Please read and FOLLOW the rules.

Regards
Michel

[Updated on: Thu, 17 April 2008 13:06]

Report message to a moderator
Re: timezlrg [message #314771 is a reply to message #314750 ] Thu, 17 April 2008 13:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 3940
Registered: November 2002
Location: California, USA
Senior Member
It appears that your value is a concatenation of the names and abbreviations from those available in the data dictionary. You can use that to extract just the name or you might also be able to just use substr and instr to extract the names if you can count on always having a space in the right place. I have demonstrated both below.

SCOTT@orcl_11g> -- timezones and abbreviations in dictionary:
SCOTT@orcl_11g> SELECT tzname, tzabbrev
  2  FROM   v$timezone_names
  3  WHERE  tzname = 'Africa/Algiers'
  4  ORDER  BY tzabbrev
  5  /

TZNAME
----------------------------------------------------------------
TZABBREV
----------------------------------------------------------------
Africa/Algiers
CEST

Africa/Algiers
CET

Africa/Algiers
LMT

Africa/Algiers
PMT

Africa/Algiers
WEST

Africa/Algiers
WET


6 rows selected.

SCOTT@orcl_11g> -- query using data dictionary:
SCOTT@orcl_11g> WITH   your_data AS
  2  	    (SELECT 'Africa/Algiers LMT' AS your_column FROM DUAL
  3  	     UNION ALL
  4  	     SELECT 'Africa/Algiers PMT' AS your_column FROM DUAL
  5  	     UNION ALL
  6  	     SELECT 'Africa/Algiers WET' AS your_column FROM DUAL
  7  	     UNION ALL
  8  	     SELECT 'Africa/Algiers WEST' AS your_column FROM DUAL
  9  	     UNION ALL
 10  	     SELECT 'Africa/Algiers CET' AS your_column FROM DUAL
 11  	     UNION ALL
 12  	     SELECT 'Africa/Algiers CEST' AS your_column FROM DUAL)
 13  SELECT your_column,
 14  	    FROM_TZ (to_timestamp ('05/01/2008 05:30','mm/dd/yyyy hh24:mi'),
 15  	      tz.tzname) at time zone 'UTC' as newtime
 16  FROM   your_data yd, v$timezone_names tz
 17  WHERE  yd.your_column = tz.tzname || ' ' || tz.tzabbrev
 18  /

YOUR_COLUMN
-------------------
NEWTIME
---------------------------------------------------------------------------
Africa/Algiers LMT
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers PMT
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers WET
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers WEST
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers CET
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers CEST
01-MAY-08 04.30.00.000000000 AM UTC


6 rows selected.

SCOTT@orcl_11g> -- query using substr and instr:
SCOTT@orcl_11g> WITH   your_data AS
  2  	    (SELECT 'Africa/Algiers LMT' AS your_column FROM DUAL
  3  	     UNION ALL
  4  	     SELECT 'Africa/Algiers PMT' AS your_column FROM DUAL
  5  	     UNION ALL
  6  	     SELECT 'Africa/Algiers WET' AS your_column FROM DUAL
  7  	     UNION ALL
  8  	     SELECT 'Africa/Algiers WEST' AS your_column FROM DUAL
  9  	     UNION ALL
 10  	     SELECT 'Africa/Algiers CET' AS your_column FROM DUAL
 11  	     UNION ALL
 12  	     SELECT 'Africa/Algiers CEST' AS your_column FROM DUAL)
 13  SELECT your_column,
 14  	    FROM_TZ (to_timestamp ('05/01/2008 05:30','mm/dd/yyyy hh24:mi'),
 15  	      SUBSTR (your_column, 1, INSTR (your_column, ' ') - 1)) at time zone 'UTC' as newtime
 16  FROM   your_data
 17  /

YOUR_COLUMN
-------------------
NEWTIME
---------------------------------------------------------------------------
Africa/Algiers LMT
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers PMT
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers WET
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers WEST
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers CET
01-MAY-08 04.30.00.000000000 AM UTC

Africa/Algiers CEST
01-MAY-08 04.30.00.000000000 AM UTC


6 rows selected.

SCOTT@orcl_11g>

Report message to a moderator
Re: timezlrg [message #316893 is a reply to message #314771 ] Mon, 28 April 2008 16:20 Go to previous messageGo to next message
Shalini1978
Messages: 7
Registered: March 2008
Junior Member
Thanks.

Does anyone know what does these different timezone abbreviations for a country/state signify? For example :

Africa/Algiers,LMT,+01:00
Africa/Algiers,PMT,+01:00
Africa/Algiers,WET,+01:00
Africa/Algiers,WEST,+01:00
Africa/Algiers,CET,+01:00
Africa/Algiers,CEST,+01:00

Africa/Algiers has timezones LMT, PMT, WET, WEST, CET and CEST. Offset is same for all i.e. +01:00. If offset is same then why different timezone abbreviations?
Report message to a moderator
Re: timezlrg [message #316900 is a reply to message #316893 ] Mon, 28 April 2008 18:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 3940
Registered: November 2002
Location: California, USA
Senior Member
Oracle documentation provides the following to explain what each abbreviation stands for and you can search Wikipedia or the Internet for definitions of the full names.

"There are 6 time zone abbreviations associated with the Africa/Algiers time zone, 3 abbreviations associated with the Africa/Cairo time zone, and 4 abbreviations associated with the Africa/Casablanca time zone. The following table shows the time zone abbreviations and their meanings.

Time Zone Abbreviation 	Meaning
LMT 	Local Mean Time
PMT 	Paris Mean Time
WET 	Western European Time
WEST 	Western European Summer Time
CET 	Central Europe Time
CEST 	Central Europe Summer Time
EET 	Eastern Europe Time
EEST 	Eastern Europe Summer Time


Note that an abbreviation can be associated with more than one time zone. For example, CET is associated with both Africa/Algiers and Africa/Casablanca, as well as time zones in Europe."
Report message to a moderator
Re: timezlrg [message #317125 is a reply to message #316900 ] Tue, 29 April 2008 11:22 Go to previous messageGo to next message
Shalini1978
Messages: 7
Registered: March 2008
Junior Member
Thanks.

Well, I am using following method to calculate UTC from a particular timezone.

SELECT FROM_TZ(to_timestamp('05/01/2008 05:30','mm/dd/yyyy hh24:mi'), 'Africa/Algiers') at time zone 'UTC' as newtime from dual

Now,'Africa/Algiers' can have timezone abbreviations as LMT, PMT, WET, WEST, CET and CEST. My from_tz method above takes just the "tzname" column and not "tzabbrev" column(see timezlrg.dat). So, how can I calculate UTC from "Africa/Algiers - WEST". In other words, I want to have a method that takes into account not just the "tzname" but "tzabbrev" also. I am not restricted to use from_tz method in my code. I would like to make sure that the UTC that I get after my calculations takes into account not only 'tzname' but 'tzabbrev' also.

I am assuming here that converted UTC time for 'Africa/Algiers - WEST' timezone may be different from the converted UTC time for 'Africa/Algiers - PMT' timezone.
Report message to a moderator
Re: timezlrg [message #317181 is a reply to message #317125 ] Tue, 29 April 2008 20:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 3940
Registered: November 2002
Location: California, USA
Senior Member
Time zones and how they are handled can get pretty confusing, but I believe that all you need or should use is the long name, not the short abbreviation. Apparently the short abbreviations are used for different times of year and are automatically calculated based on internal algorithms that Oracle uses that comply with known standards. For example, if I use US/Pacific, Oracle automatically knows that in January that is Pacific Standard Time (PST) and is therefore 8 hours prior to UTC and in June it is Pacific Daylight Time (PDT) and is therefore 7 hours prior to UTC. Please see the example below.

-- PST in January and PDT in June:
SCOTT@orcl_11g> COLUMN my_timestamp FORMAT A45
SCOTT@orcl_11g> SELECT my_timestamp,
  2  	    EXTRACT (TIMEZONE_ABBR FROM my_timestamp) AS my_tz_abbrev
  3  FROM   (SELECT TIMESTAMP '2008-01-01 10:00:00 US/Pacific' AS my_timestamp FROM DUAL
  4  	     UNION ALL
  5  	     SELECT TIMESTAMP '2008-06-01 10:00:00 US/Pacific' AS my_timestamp FROM DUAL)
  6  /

MY_TIMESTAMP                                  MY_TZ_ABBR
--------------------------------------------- ----------
01-JAN-08 10.00.00.000000000 AM US/PACIFIC    PST
01-JUN-08 10.00.00.000000000 AM US/PACIFIC    PDT

-- 8 hours difference in January and 7 hours difference in June
SCOTT@orcl_11g> COLUMN my_timestamp FORMAT A35
SCOTT@orcl_11g> COLUMN newtime	    FORMAT A35
SCOTT@orcl_11g> SELECT my_timestamp,
  2  	    FROM_TZ (my_timestamp, 'US/Pacific') AT TIME ZONE 'UTC' AS newtime
  3  FROM   (SELECT TIMESTAMP '2008-01-01 10:00:00' AS my_timestamp FROM DUAL
  4  	     UNION ALL
  5  	     SELECT TIMESTAMP '2008-06-01 10:00:00' AS my_timestamp FROM DUAL)
  6  /

MY_TIMESTAMP                        NEWTIME
----------------------------------- -----------------------------------
01-JAN-08 10.00.00.000000000 AM     01-JAN-08 06.00.00.000000000 PM UTC
01-JUN-08 10.00.00.000000000 AM     01-JUN-08 05.00.00.000000000 PM UTC

SCOTT@orcl_11g>

[Updated on: Tue, 29 April 2008 20:04]

Report message to a moderator
Re: timezlrg [message #318093 is a reply to message #317181 ] Mon, 05 May 2008 09:32 Go to previous message
Shalini1978
Messages: 7
Registered: March 2008
Junior Member
Got it. Thanks.
Report message to a moderator
Previous Topic:select *, "app" as Status from tablename
Next Topic:reading variable from text file
Goto Forum:
  

[ Syndicate this forum (XML) ] [ ]

Current Time: Sat May 17 03:54:35 CDT 2008

Total time taken to generate the page: 0.04796 seconds
.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.