Importing timestamp with zone from copy into

[monetdb-team]

Date: 2016-06-08 20:51:46 +0200
From: @skinkie
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2018-08-31 13:34:13 +0200

Comment 22199

Date: 2016-06-08 20:51:46 +0200
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Ubuntu Chromium/49.0.2623.108 Chrome/49.0.2623.108 Safari/537.36
Build Identifier:

I would like to import a timestamp with time zone from a CSV file. The ISO format is used hence: '2015-12-08T00:46:60+01:00'.

As follow up to my last bug, I also would like to see that a cast would work.

Reproducible: Always

Steps to Reproduce:

1. copy a timestamp with time zone from a CSV
2. select cast('2015-12-08T00:46:60+0200' as timestamp with time zone);

Actual Results:

Failed to import table line 1483 field 3 'timestamptz(7)' expected in '2015-12-08T00:46:60+01:00'

MonetDB 5 server v11.24.0 (64-bit, 64-bit oids, 128-bit integers)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 62.8GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.38 2015-11-23 (compiled with 8.38)
openssl: OpenSSL 1.0.2h 3 May 2016 (compiled with OpenSSL 1.0.2h 3 May 2016)
libxml2: 2.9.4 (compiled with 2.9.4)
Compiled by: skinkie@chamechaude (x86_64-pc-linux-gnu)
Compilation: gcc -O3 -pipe -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 24970

Date: 2017-02-03 17:10:04 +0100
From: @sjoerdmullender

The problem is, there are fewer than 60 seconds in a minute. In other words, the input is incorrect.

If you change the 60 into 59, the query works.

Comment 24972

Date: 2017-02-03 17:22:25 +0100
From: @skinkie

There are more than 60 seconds in a minute in a given year. Which are called leap seconds. As is explained in man mktime.

struct tm {
int tm_sec; /* Seconds (0-60) /
int tm_min; / Minutes (0-59) /
int tm_hour; / Hours (0-23) /
int tm_mday; / Day of the month (1-31) /
int tm_mon; / Month (0-11) /
int tm_year; / Year - 1900 /
int tm_wday; / Day of the week (0-6, Sunday = 0) /
int tm_yday; / Day in the year (0-365, 1 Jan = 0) /
int tm_isdst; / Daylight saving time */
};
tm_sec The number of seconds after the minute, normally in the range
0 to 59, but can be up to 60 to allow for leap seconds.

As an example program that uses mktime would give the expected output, hence the reason this is a bug.

include <stdio.h>
include <time.h>

int main ()
{
int ret;
struct tm info;
char buffer[80];

info.tm_year = 2001 - 1900;
info.tm_mon = 7 - 1;
info.tm_mday = 4;
info.tm_hour = 0;
info.tm_min = 0;
info.tm_sec = 60;
info.tm_isdst = -1;

ret = mktime(&info);
if( ret == -1 )
{
   printf("Error: unable to make time using mktime\n");
}
else
{
   strftime(buffer, sizeof(buffer), "%c", &info );
   printf(buffer);
}

return(0);

}

Comment 26593

Date: 2018-08-08 11:19:52 +0200
From: @njnes

fixed

Comment 26608

Date: 2018-08-31 13:34:13 +0200
From: @skinkie

Thanks for fixing this Niels :)