db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ken Johanson (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2235) Server doesnt support timestamps with timezone
Date Tue, 11 Sep 2007 17:18:32 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526528

Ken Johanson commented on DERBY-2235:

Daniel, although you say 'no timezone' and although the database literaly has no storage of
timezone -- there ALWAYS, always must be an implicit timezone.

A TIMESTAMP or DATETIME string '2000-01-01 12:00:00' (timezone-less) submitted form a client
in New York is NOT the same as the same value submitted from LA, unless that value was pre-converted
by the clients for the implict TZ of the database.

A database system (the server plus configs by its admin) that accepts zoneless string-datetimes
MUST have a notion of what timezone those values really exists in, EVEN if it is only implied
and not literally handled or stored by the database. Otherwise, clients sending values from
different timezone will have no coordination and the time component they send in DML/queries
will be meaningless or unreliable. 

I'm sure you agree, but I make this point to dispell the seeming 'timezones don't exist' argument,
and to persuade you to then add parsing for tz values (to convert them to the implicit timezone)
- this EASY to code AND the database doesn't need to store the timezone values -- only the
normalized (tz-less) value.

> Server doesnt support timestamps with timezone
> ----------------------------------------------
>                 Key: DERBY-2235
>                 URL: https://issues.apache.org/jira/browse/DERBY-2235
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:
>            Reporter: Ken Johanson
>            Priority: Minor
> DML with datetime literals having timzone offset data (ISO-8601):
> update tbl set dt1 = '2007-01-03 04:13:43.006 -0800'
> Causes:
> SQLException: The syntax of the string representation of a datetime value is incorrect.
> Error: -1 SQLSTATE: 22007
> I believe that even if the storage does not (does it?) support timezone storage, the
input of a TZ could be normalized (offset applied) to the default TZ.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message