Date/Timezone conversion in PL/SQL

From: Pierre-Michel Ansel <pma_at_whitepj.com>
Date: Mon, 16 Jul 2001 14:49:26 -0700
Message-ID: <GjJ47.283$Un2.89260@news.pacbell.net>

Hello everybody,

We would like to create a PL/SQL stored procedure that should do the following:

                                    |---------------------------|
 date_local                    |                                        |
date_remote


----------------------> |    PL/SQL procedure      |----------------------->

                                    |
|
                                    |---------------------------|
                                                    | tz_remote
                                                    |

The input arguments of the stored procedure are : - a date (date_local) expressed in the local timezone (timezone of the server)
- a target timezone (remote timezone)

The goal of the procedure is to convert date_local in the remote timezone.

Example:

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? Thank you for your help.

Sylvain, Pierre-Michel Received on Mon Jul 16 2001 - 16:49:26 CDT