Re: Date/Timezone conversion in PL/SQL

On Mon, 16 Jul 2001 14:49:26 -0700, "Pierre-Michel Ansel" <pma_at_whitepj.com> wrote:

>
>Local timezone : PST8PDT (US Pacific coast)
>Remote timezone : EST5EDT (US East coast)
>date_local = '07/16/2001 15:00'
>
>The stored procedure should convert date_local so that
>date_remote = '07/16/2001 18:00'
>
>We cannot use NEW_TIME as we need this procedure to work for any timezone,
>not only US ones.
>We have already implemented a solution based on an external procedure
>written in
>C language but for strategic reasons (performance related), we have been
>told to
>write a PL/SQL based procedure only.
>Has any of you already been confronted to this problem?

Yes. Many times. No need to go 9i.

Enter as parameters not the name of the time zone, but its offset to Zulu (Zulu is GMT). For example, in Australia we are Zulu+10, so I enter the date/time and +10 (positive 10). Then inside the proc do:

result=input_date+(input_offset/24);

and output the result as DATE.
Better done with a function, that way result can be of type DATE and you don't need to define "result" above as DATE.

Works like a charm.
If you absolutely need to have as input the timezone name, then build up an "index by bynary_integer" table that stores the map of timezone name to offset, inside the procedure. Then scan it and get the offset for the given timezone and use the DATE arith above.

HTH Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Tue Jul 17 2001 - 06:16:58 CDT