Changeset: 2a657d9a9a02 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2a657d9a9a02
Modified Files:
monetdb5/modules/atoms/mtime.c
sql/scripts/13_date.sql
sql/scripts/17_temporal.sql
sql/test/BugTracker-2016/Tests/epoch.Bug-3979.sql
Branch: timezone
Log Message:
The mtime functions know nothing about the SQL client time zone.
Functions such as epoch() work on a UTC time (TIMESTAMP WITH TIME
ZONE); mtime functions that deal with system time convert between
system local time (which can be different from the SQL client local
time!) and UTC as appropriate.
diffs (truncated from 639 to 300 lines):
diff --git a/monetdb5/modules/atoms/mtime.c b/monetdb5/modules/atoms/mtime.c
--- a/monetdb5/modules/atoms/mtime.c
+++ b/monetdb5/modules/atoms/mtime.c
@@ -23,6 +23,9 @@
*
* Times, both in the daytime and the timestamp types, are recorded
* with microsecond precision.
+ *
+ * Times and timestamps are all in UTC. Conversion from the system
+ * time zone where appropriate is done automatically.
*/
#include "monetdb_config.h"
@@ -37,7 +40,7 @@ extern char *strptime(const char *, cons
#define YEAR_MIN (-4712) /* 4713 BC */
#define YEAR_OFFSET (-YEAR_MIN)
-#define DTDAY_WIDTH 5 /* 1..28/29/30/31, depending on
month */
+#define DTDAY_WIDTH 5 /* 1..28/29/30/31, depending on
month/year */
#define DTDAY_SHIFT 0
#define DTMONTH_WIDTH 21 /* enough for 174761 years (and 8
months) */
#define DTMONTH_SHIFT (DTDAY_WIDTH+DTDAY_SHIFT)
@@ -79,7 +82,7 @@ static const int cumdays[13] = { /* cumu
0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365
};
#define isleapyear(y) ((y) % 4 == 0 && ((y) % 100 != 0 || (y) % 400
== 0))
-#define monthdays(y, m) ((m) != 2 ? leapdays[m] : 28 +
isleapyear(y))
+#define monthdays(y, m) (leapdays[m] - ((m) == 2 &&
!isleapyear(y)))
int TYPE_date;
int TYPE_daytime;
@@ -342,7 +345,7 @@ timestamp_fromtime(time_t timeval)
date d;
daytime t;
- if (gmtime_r(&timeval, &tm) == NULL)
+ if (timeval == (time_t) -1 || gmtime_r(&timeval, &tm) == NULL)
return timestamp_nil;
if (tm.tm_sec >= 60)
tm.tm_sec = 59; /* ignore leap seconds */
@@ -378,6 +381,7 @@ timestamp_create(date dt, daytime tm)
return mktimestamp(dt, tm);
}
+/* return the current time in UTC */
timestamp
timestamp_current(void)
{
@@ -393,17 +397,14 @@ timestamp_current(void)
#elif defined(HAVE_CLOCK_GETTIME)
struct timespec ts;
clock_gettime(CLOCK_REALTIME, &ts);
- return timestamp_add_usec(unixepoch,
- ts.tv_sec *
LL_CONSTANT(1000000)
- + ts.tv_nsec / 1000);
+ return timestamp_add_usec(timestamp_fromtime(ts.tv_sec),
+ (lng) (ts.tv_nsec /
1000));
#elif defined(HAVE_GETTIMEOFDAY)
struct timeval tv;
gettimeofday(&tv, NULL);
- return timestamp_add_usec(unixepoch,
- tv.tv_sec *
LL_CONSTANT(1000000) + tv.tv_usec);
+ return timestamp_add_usec(timestamp_fromtime(tv.tv_sec), (lng)
tv.tv_usec);
#else
- return timestamp_add_usec(unixepoch,
- (lng) time(NULL) *
LL_CONSTANT(1000000));
+ return timestamp_fromtime(time(NULL));
#endif
}
@@ -531,12 +532,6 @@ parse_date(const char *buf, date *d, boo
if ((yearneg = (buf[0] == '-')))
buf++;
if (!yearneg && !GDKisdigit(buf[0])) {
-#ifdef HAVE_SYNONYMS
- if (!synonyms) {
- GDKerror("Syntax error in date.\n");
- return -1;
- }
-#endif
yearlast = true;
sep = ' ';
} else {
@@ -546,12 +541,6 @@ parse_date(const char *buf, date *d, boo
break;
}
sep = (unsigned char) buf[pos++];
-#ifdef HAVE_SYNONYMS
- if (!synonyms && sep != '-') {
- GDKerror("Syntax error in date.\n");
- return -1;
- }
-#endif
sep = tolower(sep);
if (sep >= 'a' && sep <= 'z') {
sep = 0;
@@ -568,11 +557,6 @@ parse_date(const char *buf, date *d, boo
if (GDKisdigit(buf[pos])) {
month = (buf[pos++] - '0') + month * 10;
}
-#ifdef HAVE_SYNONYMS
- } else if (!synonyms) {
- GDKerror("Syntax error in date.\n");
- return -1;
-#endif
} else {
pos += parse_substr(&month, buf + pos, 3, MONTHS, 12);
}
@@ -606,7 +590,7 @@ parse_date(const char *buf, date *d, boo
break;
}
}
- /* handle semantic error here (returns nil in that case) */
+ /* handle semantic error here */
*d = date_create(yearneg ? -year : year, month, day);
if (is_date_nil(*d)) {
GDKerror("Semantic error in date.\n");
@@ -654,7 +638,8 @@ date_tostr(str *buf, size_t *len, const
static ssize_t
parse_daytime(const char *buf, daytime *dt, bool external)
{
- int hour, min, sec = 0, usec = 0;
+ unsigned int hour, min, sec = 0, usec = 0;
+ int n1, n2;
ssize_t pos = 0;
*dt = daytime_nil;
@@ -662,52 +647,49 @@ parse_daytime(const char *buf, daytime *
return 1;
if (external && strncmp(buf, "nil", 3) == 0)
return 3;
- if (!GDKisdigit(buf[pos])) {
- GDKerror("Syntax error in time.\n");
- return -1;
- }
- for (hour = 0; GDKisdigit(buf[pos]); pos++) {
- if (hour <= 24)
- hour = (buf[pos] - '0') + hour * 10;
- }
- if ((buf[pos++] != ':') || !GDKisdigit(buf[pos])) {
+ /* accept plenty (6) of digits, but the range is still limited */
+ switch (sscanf(buf, "%6u:%6u%n:%6u%n", &hour, &min, &n1, &sec, &n2)) {
+ default:
GDKerror("Syntax error in time.\n");
return -1;
- }
- for (min = 0; GDKisdigit(buf[pos]); pos++) {
- if (min <= 60)
- min = (buf[pos] - '0') + min * 10;
- }
- if ((buf[pos] == ':') && GDKisdigit(buf[pos + 1])) {
- for (pos++, sec = 0; GDKisdigit(buf[pos]); pos++) {
- if (sec <= 60)
- sec = (buf[pos] - '0') + sec * 10;
+ case 2:
+ /* read hour and min, but not sec */
+ if (hour >= 24 || min >= 60) {
+ GDKerror("Syntax error in time.\n");
+ return -1;
+ }
+ pos += n1;
+ break;
+ case 3:
+ /* read hour, min, and sec */
+ if (hour >= 24 || min >= 60 || sec >= 60) {
+ GDKerror("Syntax error in time.\n");
+ return -1;
}
- if ((buf[pos] == '.' || (
-#ifdef HAVE_SYNONYMS
- synonyms &&
-#endif
- buf[pos] == ':')) &&
- GDKisdigit(buf[pos + 1])) {
- pos++;
- for (int i = 0; i < 6; i++) {
+ pos += n2;
+ if (buf[pos] == '.' && GDKisdigit(buf[pos+1])) {
+ if (sscanf(buf + pos + 1, "%7u%n", &usec, &n1) < 1) {
+ /* cannot happen: buf[pos+1] is a digit */
+ GDKerror("Syntax error in time.\n");
+ return -1;
+ }
+ pos += n1 + 1;
+ while (n1 < 6) {
usec *= 10;
- if (GDKisdigit(buf[pos])) {
- usec += buf[pos] - '0';
- pos++;
- }
+ n1++;
}
-#ifndef TRUNCATE_NUMBERS
- if (GDKisdigit(buf[pos]) && buf[pos] >= '5') {
- /* round the value */
- if (++usec == 1000000) {
+ if (n1 == 7) {
+#ifdef TRUNCATE_NUMBERS
+ usec /= 10;
+#else
+ usec = (usec + 5) / 10;
+ if (usec == 1000000) {
usec = 0;
if (++sec == 60) {
sec = 0;
if (++min == 60) {
min = 0;
if (++hour == 24) {
- /* forget about
rounding if it doesn't fit */
hour = 23;
min = 59;
sec = 59;
@@ -716,13 +698,14 @@ parse_daytime(const char *buf, daytime *
}
}
}
+#endif
}
-#endif
+ /* ignore excess digits */
while (GDKisdigit(buf[pos]))
pos++;
}
+ break;
}
- /* handle semantic error here (returns nil in that case) */
*dt = daytime_create(hour, min, sec, usec);
if (is_daytime_nil(*dt)) {
GDKerror("Semantic error in time.\n");
@@ -747,10 +730,11 @@ ssize_t
daytime_tz_fromstr(const char *buf, size_t *len, daytime **ret, bool external)
{
const char *s = buf;
- ssize_t pos = daytime_fromstr(s, len, ret, external);
+ ssize_t pos;
daytime val;
int offset = 0;
+ pos = daytime_fromstr(s, len, ret, external);
if (pos < 0 || is_daytime_nil(**ret))
return pos;
@@ -758,7 +742,7 @@ daytime_tz_fromstr(const char *buf, size
pos = 0;
while (GDKisspace(*s))
s++;
- /* in case of gmt we need to add the time zone */
+ /* for GMT we need to add the time zone */
if (fleximatch(s, "gmt", 0) == 3) {
s += 3;
}
@@ -767,17 +751,18 @@ daytime_tz_fromstr(const char *buf, size
((s[3] == ':' && GDKisdigit(s[5])) || GDKisdigit(s[pos = 3]))) {
offset = (((s[1] - '0') * 10 + (s[2] - '0')) * 60 + (s[pos] -
'0') * 10 + (s[pos + 1] - '0')) * 60;
pos += 2;
- if (s[0] != '-')
- offset = -offset;
+ if (s[0] == '+')
+ offset = -offset; /* East of Greenwich */
s += pos;
}
- val = **ret + (lng) offset * 1000000;
+ /* convert to UTC */
+ val = **ret + offset * LL_CONSTANT(1000000);
if (val < 0)
- **ret = DAY_USEC + val;
+ val += DAY_USEC;
else if (val >= DAY_USEC)
- **ret = val - DAY_USEC;
- else
- **ret = val;
+ val -= DAY_USEC;
+ /* and return */
+ **ret = val;
return (ssize_t) (s - buf);
}
@@ -910,7 +895,7 @@ timestamp_tz_fromstr(const char *buf, si
pos = 0;
while (GDKisspace(*s))
s++;
- /* incase of gmt we need to add the time zone */
+ /* in case of gmt we need to add the time zone */
if (fleximatch(s, "gmt", 0) == 3) {
s += 3;
}
@@ -1581,8 +1566,10 @@ MTIMEseconds_since_epoch(int *ret, const
return MAL_SUCCEED;
}
-#define mktsfromsec(sec) timestamp_add_usec(unixepoch, sec *
LL_CONSTANT(1000000))
-#define mktsfrommsec(msec) timestamp_add_usec(unixepoch, msec * 1000)
+#define mktsfromsec(sec) timestamp_add_usec(unixepoch,
\
+
(sec) * LL_CONSTANT(1000000))
+#define mktsfrommsec(msec) timestamp_add_usec(unixepoch,
\
+
(msec) * LL_CONSTANT(1000))
func1(MTIMEtimestamp_fromsecond, MTIMEtimestamp_fromsecond_bulk, "timestamp",
int, timestamp, mktsfromsec, COPYFLAGS)
func1(MTIMEtimestamp_frommsec, MTIMEtimestamp_frommsec_bulk, "timestamp", lng,
timestamp, mktsfrommsec, COPYFLAGS)
@@ -1655,11 +1642,14 @@ MTIMEdaytime_fromseconds_bulk(bat *ret,
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
