region based Date handling

[spicysudhi]

Hi,

My database server set to GMT time. Users are from Asia, Europe and North america.

When a date column is inserted with current date during a transaction, it is currently inserted as system date (SYSDATE/TIMESTAMP).

What is the best approach for the users to see the date/time in their local timezones and formats?

any oracle based functions/settings or do i hav to do manual calculations based on user's timezone?

thanks for ur inputs.

regards,
Sudhi

 

[SantaMufasa]

Sudhi,

I recommend your Googling for "Oracle timestamp". This datatype provides timezone support and time-keeping granularity down to one billionth of a second.

Let us know if this helps resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oradba101]

Hi, Sudhi

This is from the Oracle documentation. Hopefully it will point you in the right direction

============================================================

TIMESTAMP WITH LOCAL TIME ZONE Datatype

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone offset in its value. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.

Specify the TIMESTAMP WITH LOCAL TIME ZONE datatype as follows:
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE


where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value can be a number in the range 0 to 9. The default is 6.

Oracle time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle time zone data may not reflect the most recent data available at this site.

See Also:

Oracle Database Globalization Support Guide for more information on Oracle time zone data

Oracle Database Application Developer's Guide - Fundamentals for examples of using this datatype and CAST for information on converting character data to TIMESTAMP WITH LOCAL TIME ZONE

============================================================

Regards,


William Chadbourne
Oracle DBA