DST Compliance

[tsmithnj]

Will someone (Serge wink-wink) weigh in on a DST question?

I understand completely that DB2 relies on the operating system to
retrieve the date. That said, DB2 will allow me to add time to a
timestamp to the extent that DB2 will return an invalid time:

$ db2 values current timestamp + 23 days + 12 hours

1
--------------------------
2007-03-11-02.53.27.895029

1 record(s) selected.
(this is a patched server, perl returns 2007-03-11-03.53.27.895029, as
a control I ran this test for the "old" DST dates and receivedd the
same results)

So if I put on my IBM hat, I would argue that once we pass 01:59:59 on
3/11, db2 will continue to return the correct timestamp, however if I
want to be a DST purist, then acknowledging
"2007-03-11-02.53.27.895029" even exists, and allowing it to be stored
as a timestamp is no more accurate than allowing a garbled character
string to be stored as a timestamp.
t

[Serge Rielau]

ts******@gmail.com wrote:

Will someone (Serge wink-wink) weigh in on a DST question?

I understand completely that DB2 relies on the operating system to
retrieve the date. That said, DB2 will allow me to add time to a
timestamp to the extent that DB2 will return an invalid time:

$ db2 values current timestamp + 23 days + 12 hours

1
--------------------------
2007-03-11-02.53.27.895029

1 record(s) selected.
(this is a patched server, perl returns 2007-03-11-03.53.27.895029, as
a control I ran this test for the "old" DST dates and receivedd the
same results)

So if I put on my IBM hat, I would argue that once we pass 01:59:59 on
3/11, db2 will continue to return the correct timestamp, however if I
want to be a DST purist, then acknowledging
"2007-03-11-02.53.27.895029" even exists, and allowing it to be stored
as a timestamp is no more accurate than allowing a garbled character
string to be stored as a timestamp.

I have no clue what the rules are here...
Don't even know whom to ask (which is rare)...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[memmerto]

On Feb 15, 4:55 pm, Serge Rielau <[e-mail]:

[e-mail] wrote:

Will someone (Serge wink-wink) weigh in on a DST question?

I understand completely that DB2 relies on the operating system to
retrieve the date. That said, DB2 will allow me to add time to a
timestamp to the extent that DB2 will return an invalid time:

$ db2 values current timestamp + 23 days + 12 hours

1
--------------------------
2007-03-11-02.53.27.895029

1 record(s) selected.

(this is a patched server, perl returns 2007-03-11-03.53.27.895029, as
a control I ran this test for the "old" DST dates and receivedd the
same results)

So if I put on my IBM hat, I would argue that once we pass 01:59:59 on
3/11, db2 will continue to return the correct timestamp, however if I
want to be a DST purist, then acknowledging
"2007-03-11-02.53.27.895029" even exists, and allowing it to be stored
as a timestamp is no more accurate than allowing a garbled character
string to be stored as a timestamp.

I have no clue what the rules are here...
Don't even know whom to ask (which is rare)...

This is a very interesting question. Here's my take on the situation.

While DB2 relies the OS to get the current time (and to handle
adjustments such as DST and timezone), that's the extent of its
interaction with the OS. All date arithmetic (ie, + 23 days + 12
hours) is handled internally to the engine, and we don't further
validate the resultant date to see if it falls into a DST blackhole.

This is yet another example of why it's best to record dates in any
database application using UTC, and to apply the DST/timezone
conversions on the client. Times, just like dates, currency and
floating-point values are subject to locale conversions, and timezone/
DST is very locale-centric.

Matt Emmerton

[tsmithnj]

On Feb 15, 7:46 pm, "[e-mail]" <[e-mail]:

On Feb 15, 4:55 pm, Serge Rielau <[e-mail]:

[e-mail] wrote:

Will someone (Serge wink-wink) weigh in on a DST question?

I understand completely that DB2 relies on the operating system to
retrieve the date. That said, DB2 will allow me to add time to a
timestamp to the extent that DB2 will return an invalid time:

$ db2 values current timestamp + 23 days + 12 hours

1
--------------------------
2007-03-11-02.53.27.895029

1 record(s) selected.

(this is a patched server, perl returns 2007-03-11-03.53.27.895029, as
a control I ran this test for the "old" DST dates and receivedd the
same results)

So if I put on my IBM hat, I would argue that once we pass 01:59:59 on
3/11, db2 will continue to return the correct timestamp, however if I
want to be a DST purist, then acknowledging
"2007-03-11-02.53.27.895029" even exists, and allowing it to be stored
as a timestamp is no more accurate than allowing a garbled character
string to be stored as a timestamp.

I have no clue what the rules are here...
Don't even know whom to ask (which is rare)...

This is a very interesting question. Here's my take on the situation.

While DB2 relies the OS to get the current time (and to handle
adjustments such as DST and timezone), that's the extent of its
interaction with the OS. All date arithmetic (ie, + 23 days + 12
hours) is handled internally to the engine, and we don't further
validate the resultant date to see if it falls into a DST blackhole.

This is yet another example of why it's best to record dates in any
database application using UTC, and to apply the DST/timezone
conversions on the client. Times, just like dates, currency and
floating-point values are subject to locale conversions, and timezone/
DST is very locale-centric.

Matt Emmerton- Hide quoted text -

- Show quoted text -

So if I convert to UTC, is it safe to say the CURRENT TIMEZONE special
register uses TZ so as long as I'm OS patched I am OK ?