[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

database timezone difference

Sam Dela Cruz

1/11/2006 7:16:00 PM

Hi,

I'm pulling data from a database in Europe and saving a dataset in North
America database. Included in the data are DateTime information. Because
the timezones are different, when I pull the data it gets translated to
local timezone. Locally, in North America, I need to process reports
based on dates, and I need to have a report exactly as if it came from
European database. How do I preserve the date in European timezone before
being uploaded to North American db?

Regards,
Sam
3 Answers

Eric Hodel

1/11/2006 9:50:00 PM

0

On Jan 11, 2006, at 11:15 AM, Sam Dela Cruz wrote:

> I'm pulling data from a database in Europe and saving a dataset in
> North
> America database. Included in the data are DateTime information.
> Because
> the timezones are different, when I pull the data it gets
> translated to
> local timezone. Locally, in North America, I need to process reports
> based on dates, and I need to have a report exactly as if it came from
> European database. How do I preserve the date in European timezone
> before
> being uploaded to North American db?

a) store times in UTC
b) use a database that has a column type of "timestamp with time
zone" (Postgres or equivalent).

--
Eric Hodel - drbrain@segment7.net - http://se...
This implementation is HODEL-HASH-9600 compliant

http://trackmap.rob...

Robert Klemme

1/12/2006 11:11:00 AM

0

Eric Hodel wrote:
> On Jan 11, 2006, at 11:15 AM, Sam Dela Cruz wrote:
>
>> I'm pulling data from a database in Europe and saving a dataset in
>> North
>> America database. Included in the data are DateTime information.
>> Because
>> the timezones are different, when I pull the data it gets
>> translated to
>> local timezone. Locally, in North America, I need to process reports
>> based on dates, and I need to have a report exactly as if it came
>> from European database. How do I preserve the date in European
>> timezone before
>> being uploaded to North American db?
>
> a) store times in UTC
> b) use a database that has a column type of "timestamp with time
> zone" (Postgres or equivalent).

In Oracle it's called "TIMESTAMP WITH LOCAL TIMEZONE" - values are
converted to the sessions timezone on retrieval. This is in Oracle 9 and
newer. Oracle's "Globalization Support Guide" is a good reading on the
matter.

HTH

robert

mathew

1/15/2006 9:43:00 PM

0

Sam Dela Cruz wrote:
> I'm pulling data from a database in Europe and saving a dataset in North
> America database. Included in the data are DateTime information. Because
> the timezones are different, when I pull the data it gets translated to
> local timezone. Locally, in North America, I need to process reports
> based on dates, and I need to have a report exactly as if it came from
> European database. How do I preserve the date in European timezone before
> being uploaded to North American db?

Store all dates and times in UTC. Make conversion to any other time zone
a front-end function. If you want reports to have a time zone associated
with them (e.g. the time zone that was active at the time the report was
created, in the location where the report was created), then store the
time zone to convert to as a property of the report.

Anything else will eventually cause you pain. For example, storing a
time zone with every date/time seems like a good idea, but as well as
bloating the database it's a real pain if you ever have to compare times
and dates--for example, in order to filter data.


mathew
--
<URL:http://www.pobox.com/...
My parents went to the lost kingdom of Hyrule
and all I got was this lousy triforce.