No error is given when incorrect timezone value is specified for a timetz column

[monetdb-team]

Date: 2015-03-05 18:30:56 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

Last updated: 2015-08-28 13:42:10 +0200

Comment 20692

Date: 2015-03-05 18:30:56 +0100
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:35.0) Gecko/20100101 Firefox/35.0
Build Identifier:

Also it appears that any specified timezone value (either codes such as PDT, GMT, CET, DST or numeric +/-HH:MM representations such as +01:00 +04:30 -07:00) is not honered during conversion of string to timetz internal value.

Reproducible: Always

Steps to Reproduce:

1. Start mserver5
2. Start mclient
3. execute following SQL:
   CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone);
   INSERT INTO TIMETZ_TBL VALUES ('00:00 bla blah');
   INSERT INTO TIMETZ_TBL VALUES ('00:01 PDT');
   INSERT INTO TIMETZ_TBL VALUES ('00:02 GMT');
   INSERT INTO TIMETZ_TBL VALUES ('00:03 CET');
   INSERT INTO TIMETZ_TBL VALUES ('00:04 DST');
   SELECT f1, cast(f1 as varchar(30)) as time_str FROM TIMETZ_TBL;

INSERT INTO TIMETZ_TBL VALUES ('00:00:20 +00:00 bla blah');
INSERT INTO TIMETZ_TBL VALUES ('01:01:20 +01:00');
INSERT INTO TIMETZ_TBL VALUES ('02:02:20 +04:30');
INSERT INTO TIMETZ_TBL VALUES ('03:03:20 -07:00');
SELECT f1, cast(f1 as varchar(30)) as time_str FROM TIMETZ_TBL;

Actual Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.20.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone);
operation successful (1.909ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('00:00 bla blah');
1 affected row (1.115ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('00:01 PDT');
1 affected row (0.794ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('00:02 GMT');
1 affected row (0.765ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('00:03 CET');
1 affected row (0.779ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('00:04 DST');
1 affected row (0.829ms)
sql>SELECT f1, cast(f1 as varchar(30)) as time_str FROM TIMETZ_TBL;
+-------------------+-------------------+
| f1 | time_str |
+===================+===================+
| 01:00:00.00+01:00 | 01:00:00.00+01:00 |
| 01:01:00.00+01:00 | 01:01:00.00+01:00 |
| 01:02:00.00+01:00 | 01:02:00.00+01:00 |
| 01:03:00.00+01:00 | 01:03:00.00+01:00 |
| 01:04:00.00+01:00 | 01:04:00.00+01:00 |
+-------------------+-------------------+
5 tuples (3.646ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('00:00:20 +00:00 bla blah');
1 affected row (1.160ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('01:01:20 +01:00');
1 affected row (0.864ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('02:02:20 +04:30');
1 affected row (0.729ms)
sql>INSERT INTO TIMETZ_TBL VALUES ('03:03:20 -07:00');
1 affected row (0.783ms)
sql>SELECT f1, cast(f1 as varchar(30)) as time_str FROM TIMETZ_TBL;
+-------------------+-------------------+
| f1 | time_str |
+===================+===================+
| 01:00:00.00+01:00 | 01:00:00.00+01:00 |
| 01:01:00.00+01:00 | 01:01:00.00+01:00 |
| 01:02:00.00+01:00 | 01:02:00.00+01:00 |
| 01:03:00.00+01:00 | 01:03:00.00+01:00 |
| 01:04:00.00+01:00 | 01:04:00.00+01:00 |
| 01:00:20.00+01:00 | 01:00:20.00+01:00 |
| 02:01:20.00+01:00 | 02:01:20.00+01:00 |
| 03:02:20.00+01:00 | 03:02:20.00+01:00 |
| 04:03:20.00+01:00 | 04:03:20.00+01:00 |
+-------------------+-------------------+
9 tuples (1.116ms)
sql>\q

Expected Results:

errors for the "bla blah" values
correct interpretation and conversion to UTC for the other values

It is probably also a problem for the timestamptz data type

Comment 20709

Date: 2015-03-11 19:04:56 +0100
From: @njnes

now we return an error on when only a part of the input string is parsed

Comment 21204

Date: 2015-08-28 13:42:10 +0200
From: @sjoerdmullender

Jul2015 has been released.