You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Ken Johanson (JIRA)" <ji...@apache.org> on 2007/01/12 07:56:27 UTC
[jira] Created: (DERBY-2235) Server doesnt support timestamps with
timezone
Server doesnt support timestamps with timezone
----------------------------------------------
Key: DERBY-2235
URL: https://issues.apache.org/jira/browse/DERBY-2235
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.2.0
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.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Ken Johanson (JIRA)" <ji...@apache.org>.
[ 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: 10.2.2.0
> 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.
Re: [jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Lance J. Andersen" <La...@Sun.COM>.
fwiw,
for JDBC.next i am looking at adding support for time and timestamp with TZ.
-lance
Daniel John Debrunner (JIRA) wrote:
> [ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12523890 ]
>
> Daniel John Debrunner commented on DERBY-2235:
> ----------------------------------------------
>
> Ken> ... if a timezone-less string ordinarily defaults to GMT. Or is a tz-less string defaults to the system's tz ...
>
> It's neither. Derby's TIMESTAMP values are TIMESTAMP with no associated timezone information.
>
> For information on how the datetime values interact with JDBC see:
>
> http://db.apache.org/derby/docs/10.3/publishedapi/jdbc3/org/apache/derby/jdbc/package-summary.html
>
>
>
>
>> 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: 10.2.2.0
>> 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.
>>
>
>
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12523890 ]
Daniel John Debrunner commented on DERBY-2235:
----------------------------------------------
Ken> ... if a timezone-less string ordinarily defaults to GMT. Or is a tz-less string defaults to the system's tz ...
It's neither. Derby's TIMESTAMP values are TIMESTAMP with no associated timezone information.
For information on how the datetime values interact with JDBC see:
http://db.apache.org/derby/docs/10.3/publishedapi/jdbc3/org/apache/derby/jdbc/package-summary.html
> 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: 10.2.2.0
> 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.
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526509 ]
Daniel John Debrunner commented on DERBY-2235:
----------------------------------------------
>>if the string '2007-01-03 04:13:43' is converted to a TIMESTAMP in SQL then it will always represent '2007-01-03 04:13:43'
> In what timezone? GMT? The server's?
No time zone, Derby's TIMESTAMP values are TIMESTAMP with no associated timezone information.
It's just 04:13 with no associated timezone. If the server's timezone was change to something else, the value remains at 04:13 as it is not associated with a timezone.
> 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: 10.2.2.0
> 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.
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Ken Johanson (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12523915 ]
Ken Johanson commented on DERBY-2235:
-------------------------------------
>if the string '2007-01-03 04:13:43' is converted to a TIMESTAMP in SQL then it will always represent '2007-01-03 04:13:43'
In what timezone? GMT? The server's?
I suspect (admittedly not having looked at the code nor tried Derby lately) the actual behavior is that is will always be treated as the server's timezone (if using a SimpleDateFormat and iso8601 format string, and not overriding the default timezone, which DateFormat uses from TimeZone.getDefault()).
As I recall tz-less strings SHOULD (or MUST) be interpreted as the being in the TZ of the receiving host (the server in this case), so if the above is true then TZ-less strings are working okay.
For datetime and possibly timestamp, if a zone-offset IS provided in the string I would argue that the server should have the option to accept this, even if it will not store the TZ info.
> 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: 10.2.2.0
> 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.
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Ken Johanson (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12523874 ]
Ken Johanson commented on DERBY-2235:
-------------------------------------
I believe that until/if timezone storage is supported, the server should treat it as whatever the default (timezone-less) string representaion of an iso8601. In otherwords it should store the '2007-01-03 04:13:43.006' value, minus 8 hours, if a timezone-less string ordinarily defaults to GMT. Or is a tz-less string defaults to the system's tz, i.e if the tz-less string defaults to EDT and a PDT timezone is entered, the offset from EDT to PDT should be used to create the value that EDT would use:
1) server runs in EDT (ie INSERT 2007-01-03 04:13:43 = 2007-01-03 09:13:43 GMT)
2) server recieves INSERT '2007-01-03 01:00:00.006.006 -0800', translates to 2007-01-03 04:00:00.006, ie 2007-01-03 09:00:00.006.
It would be argued by some that if a TZ value is present but cannot be stored an error should be reported, but other users would elect (given config option) to just have to auto-computed to real time (without tz).
> 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: 10.2.2.0
> 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.
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12528119 ]
Daniel John Debrunner commented on DERBY-2235:
----------------------------------------------
If one took the string ''2007-01-03 04:13:43.006 -0800' as equivalent to a TIMESTAMP WITH TIME ZONE value then following the SQL Standard (section 4.6.2) then a conversion to a TIMESTAMP WITHOUT TIME ZONE (which is what Derby supports) will always result in:
2007-01-03 04:13:43.006
regardless of any server time zone.
TIMESTAMP WITH TIME ZONE ->> TIMESTAMP WITHOUT TIME ZONE = SV.UTC + SV.TZ
SV is the value to be converted, in this case the string which is broken into two logical parts:
SV.UTC = 2007-01-03 12:13:43.006 (universal time)
SV.TZ = -0800 (offset from universal time)
SV.UTC + SV.TZ = 2007-01-03 04:13:43.006
> 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: 10.2.2.0
> 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.
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Ken Johanson (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12523876 ]
Ken Johanson commented on DERBY-2235:
-------------------------------------
I should clarify (though it may already be obvious to some folks) that this issue applies to any datetime type, not just the 'timestamp' type that I mentioned.
Also I should have stated:
In otherwords it should store the '2007-01-03 04:13:43.006' value, PLUS 8 hours, if a timezone-less string ordinarily defaults to GMT
not
"In otherwords it should store the '2007-01-03 04:13:43.006' value, minus 8 hours, if a timezone-less string ordinarily defaults to GMT"
> 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: 10.2.2.0
> 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.
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12523896 ]
Daniel John Debrunner commented on DERBY-2235:
----------------------------------------------
To be clear, if the string '2007-01-03 04:13:43' is converted to a TIMESTAMP in SQL then it will always represent '2007-01-03 04:13:43' regardless of any timezone of the server or client.
E.g. INSERT INTO T VALUES CAST ('2007-01-03 04:13:43' AS TIMESTAMP)
> 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: 10.2.2.0
> 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.
[jira] Commented: (DERBY-2235) Server doesnt support timestamps
with timezone
Posted by "Bernt M. Johnsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12528044 ]
Bernt M. Johnsen commented on DERBY-2235:
-----------------------------------------
Brorwsing through the SQL2003 standard, I find that Dan is right. A timestamp without timezone has no associated timezone, not even implicit. So '2000-01-01 12:00:00' in LA is '2000-01-01 12:00:00' in NY since no timezone is associated with the datatype. Then it is the application's problem to interpret the values. The standard *does* say, however, that the current SQL-session's timezone is applied if timezone is required (e.g when cast to TIMESTAMP WITH TIMEZONE). Quote from the standard:
If <time zone interval> is not specified, then no assumption is made about time zone displacement. However, should a time zone
displacement be required during subsequent processing, the current default time zone displacement of the SQL-session will be
applied at that time.
> 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: 10.2.2.0
> 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.
[jira] Updated: (DERBY-2235) Server doesnt support timestamps with
timezone
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2235?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel John Debrunner updated DERBY-2235:
-----------------------------------------
Issue Type: Improvement (was: Bug)
This would be an improvement, not a bug, though I'm not sure what TIMESTAMP value should be inserted here.
Derby's TIMESTAMP values are TIMESTAMP with no associated timezone information.
In the above example, would the timestamp inserted be:
2007-01-03 04:13:43.006 (local time in -0800 timezone)
2007-01-03 12:13:43.006 (GMT time)
2007-01-03 XX:YY:43.006 (conversion from -0800 to the server jvm's local time)
> 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: 10.2.2.0
> 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.