Casting DateTime to OracleTimeStampTZ

Question

Shortly before saving a DateTime to the datebase I'm casting a DateTime into an OracleTimeStampTZ. This converts the DateTime into an OracleTimeStampTZ in the current timezone, which is cool, that's what I want. Except the timezone is represented as a time off set (+13, since I'm in NZ).

Now I'm a little afraid that this is going to bite me in the ass with regards to daylight savings. I'd much rather have the OracleTimeStampTZ.TimeZone represented as a named timezone (and therefore a named timezone in the database).

For consistency I want to use a the timezone as determined by .NET (IE using TimeZoneInfo.Local).

The problem is that TimeZoneInfo doesn't seem to contain timezone name that match the oracle timezone names. Also, the OracleTimeStampTZ.TimeZone property is readonly.

Anyone know a nice way of achieving this?

Thanks in advance

== EDIT explaining my worries about DST ==

OK, what I'm worried about is the following: Say I save a date right now in the database for an event that is to happen in a year and a half from now (on the other side of daylight savings anyway).

Say the date/time I save is 16:00 +13. When we get to the other side of daylight savings the offset in my country might be +12, meaning that in my local timezone the date I previously saved is considered as 15:00, since my offset has changed but the stored one has not.

From what I understand, if the named timezone is stored then when we come to evaluate the date on the other side of daylight savings we can determine the correct offset to use based on whether it is DST or not.

Is what I have described correct?

Answer 1

I'm thinking you're probably Ok as is. Can you sketch out a use case for the problem you're worried about? IIRC, Oracle is daylight savings time aware, and (since the date is being stored) I'm not sure how you could lose information--you'll know if daylight savings time was in effect, right?

-- MarkusQ

So the first thing I'd try is saving such a date and seeing what the offset it. In other words, the numeric offset used for a timezone may be (correctly) a function of the date.

I suppose another alternative, if this doesn't work, would be to save the time zone name in an enum column and reconstruct the time as needed. Not pretty, but...