Changeset: 534d17c17c1c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=534d17c17c1c
Modified Files:
monetdb5/modules/atoms/mtime.c
monetdb5/modules/atoms/mtime.h
sql/backends/monet5/sql_bat2time.c
sql/test/BugTracker-2019/Tests/date_trunc.sql
sql/test/BugTracker-2019/Tests/date_trunc.stable.out
Branch: datetrunc
Log Message:
Scalar truncation done (except for week)
diffs (242 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
@@ -1768,6 +1768,19 @@ MTIMEdate_extract_day(int *ret, const da
return MAL_SUCCEED;
}
+str
+MTIMEdate_extract_ymd(int *year, int *month, int *day, const date *v)
+{
+ if (date_isnil(*v)) {
+ *year = int_nil;
+ *month = int_nil;
+ *day = int_nil;
+ } else {
+ fromdate(*v, day, month, year);
+ }
+ return MAL_SUCCEED;
+}
+
/* Returns N where d is the Nth day of the year (january 1 returns 1). */
str
MTIMEdate_extract_dayofyear(int *ret, const date *v)
diff --git a/monetdb5/modules/atoms/mtime.h b/monetdb5/modules/atoms/mtime.h
--- a/monetdb5/modules/atoms/mtime.h
+++ b/monetdb5/modules/atoms/mtime.h
@@ -246,6 +246,8 @@ mal_export str MTIMEdate_extract_year_bu
mal_export str MTIMEdate_extract_quarter_bulk(bat *ret, const bat *bid);
mal_export str MTIMEdate_extract_month_bulk(bat *ret, const bat *bid);
mal_export str MTIMEdate_extract_day_bulk(bat *ret, const bat *bid);
+mal_export str MTIMEdate_extract_ymd(int *year, int *month, int *day, const
date *v);
+
mal_export str MTIMEdaytime_extract_hours_bulk(bat *ret, const bat *bid);
mal_export str MTIMEdaytime_extract_minutes_bulk(bat *ret, const bat *bid);
diff --git a/sql/backends/monet5/sql_bat2time.c
b/sql/backends/monet5/sql_bat2time.c
--- a/sql/backends/monet5/sql_bat2time.c
+++ b/sql/backends/monet5/sql_bat2time.c
@@ -303,7 +303,7 @@ static int truncate_check(const str *sca
nils++; \
} else { \
ts = bt[0];
\
- ts.msecs = (ts.msecs / DIVISOR) * DIVISOR; \
+ ts.msecs = (int) ((lng)ts.msecs / (lng)DIVISOR)
* (lng)DIVISOR; \
dt[lo] = ts;
\
} }
@@ -337,12 +337,23 @@ bat_date_trunc(bat *res, const str *scal
hi = lo + BATcount(b);
date_trunc_time_loop("microseconds", TIMESTAMP, 1)
- date_trunc_time_loop("milliseconds", TIMESTAMP, 1000)
- date_trunc_time_loop("seconds", TIMESTAMP, (1000 * 60))
- date_trunc_time_loop("minute", TIMESTAMP, (1000 * 60 * 60))
- date_trunc_time_loop("hour", TIMESTAMP, (1000 * 60 * 60 * 24))
+ date_trunc_time_loop("milliseconds", TIMESTAMP, 1)
+ date_trunc_time_loop("second", TIMESTAMP, (1000 ))
+ date_trunc_time_loop("minute", TIMESTAMP, (1000 * 60))
+ date_trunc_time_loop("hour", TIMESTAMP, (1000 * 60 * 24))
+
+ if ( strcmp(*scale, "day") == 0){
+ for( ; lo < hi; lo++)
+ if (timestamp_isnil(bt[lo])) {
+ dt[lo] = *timestamp_nil;
+ } else {
+ ts = bt[lo];
+ ts.msecs = 0;
+ dt[lo] = ts;
+ } }
// week
+ // month
// quarter
// decade
// century
@@ -366,7 +377,7 @@ bat_date_trunc(bat *res, const str *scal
*dt = *timestamp_nil; \
} else { \
ts = *bt; \
- ts.msecs = (ts.msecs / DIVISOR) * DIVISOR; \
+ ts.msecs = (int) ((lng)ts.msecs / (lng)DIVISOR) *
(lng)DIVISOR; \
*dt = ts; \
} }
@@ -375,19 +386,93 @@ date_trunc(timestamp *dt, const str *sca
{
str msg = MAL_SUCCEED;
timestamp ts;
+ int y, m, d, one = 1;
if (truncate_check(scale) == 0)
throw(SQL, "sql.truncate", SQLSTATE(HY001) "Improper directive
");
date_trunc_single_time("microseconds", TIMESTAMP, 1)
- date_trunc_single_time("milliseconds", TIMESTAMP, 1000)
- date_trunc_single_time("seconds", TIMESTAMP, (1000 * 60))
- date_trunc_single_time("minute", TIMESTAMP, (1000 * 60 * 60))
- date_trunc_single_time("hour", TIMESTAMP, (1000 * 60 * 60 * 24))
+ date_trunc_single_time("milliseconds", TIMESTAMP, 1)
+ date_trunc_single_time("second", TIMESTAMP, (1000))
+ date_trunc_single_time("minute", TIMESTAMP, (1000 * 60))
+ date_trunc_single_time("hour", TIMESTAMP, (1000 * 60 * 24))
+ if ( strcmp(*scale, "day") == 0){
+ if (timestamp_isnil(*bt)) {
+ *dt = *timestamp_nil;
+ } else {
+ ts = *bt;
+ ts.msecs = 0;
+ *dt = ts;
+ } }
// week
- // quarter
- // decade
- // century
- // millenium
+ if ( strcmp(*scale, "month") == 0){
+ if (timestamp_isnil(*bt)) {
+ *dt = *timestamp_nil;
+ } else {
+ ts = *bt;
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ MTIMEdate_create(&ts.days, &y, &m, &one);
+ *dt = ts;
+ } }
+
+ if ( strcmp(*scale, "quarter") == 0){
+ if (timestamp_isnil(*bt)) {
+ *dt = *timestamp_nil;
+ } else {
+ ts = *bt;
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ m = m/4 + 1;
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ *dt = ts;
+ } }
+
+ if ( strcmp(*scale, "year") == 0){
+ if (timestamp_isnil(*bt)) {
+ *dt = *timestamp_nil;
+ } else {
+ ts = *bt;
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ *dt = ts;
+ } }
+
+ if ( strcmp(*scale, "decade") == 0){
+ if (timestamp_isnil(*bt)) {
+ *dt = *timestamp_nil;
+ } else {
+ ts = *bt;
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ y = (y /10 ) *10;
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ *dt = ts;
+ } }
+
+ if ( strcmp(*scale, "century") == 0){
+ if (timestamp_isnil(*bt)) {
+ *dt = *timestamp_nil;
+ } else {
+ ts = *bt;
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ y = (y /100 ) *100 +1;
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ *dt = ts;
+ } }
+
+ if ( strcmp(*scale, "century") == 0){
+ if (timestamp_isnil(*bt)) {
+ *dt = *timestamp_nil;
+ } else {
+ ts = *bt;
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ y = (y /1000 ) *1000 +1;
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ *dt = ts;
+ } }
return msg;
}
diff --git a/sql/test/BugTracker-2019/Tests/date_trunc.sql
b/sql/test/BugTracker-2019/Tests/date_trunc.sql
--- a/sql/test/BugTracker-2019/Tests/date_trunc.sql
+++ b/sql/test/BugTracker-2019/Tests/date_trunc.sql
@@ -1,3 +1,5 @@
+select timestamp '2019-02-17 02:08:12.345678';
+
select date_trunc('microseconds', timestamp '2019-02-17 02:08:12.345678');
select date_trunc('milliseconds', timestamp '2019-02-17 02:08:12.345678');
select date_trunc('second', timestamp '2019-02-17 02:08:12.345678');
diff --git a/sql/test/BugTracker-2019/Tests/date_trunc.stable.out
b/sql/test/BugTracker-2019/Tests/date_trunc.stable.out
--- a/sql/test/BugTracker-2019/Tests/date_trunc.stable.out
+++ b/sql/test/BugTracker-2019/Tests/date_trunc.stable.out
@@ -65,37 +65,37 @@ Ready.
% L2 # name
% timestamp # type
% 26 # length
-[ ??? ]
+[ 2019-02-11 00:00:00.000000 ]
#select date_trunc('month', timestamp '2019-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-00 00:00:00.000000 ]
+[ 2019-02-01 00:00:00.000000 ]
#select date_trunc('quarter', timestamp '2019-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ ??? 00:00:00.000000 ]
+[ 2019-01-01 00:00:00.000000 ]
#select date_trunc('year', timestamp '2019-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-00-00 00:00:00.000000 ]
+[ 2019-01-01 00:00:00.000000 ]
#select date_trunc('decade', timestamp '2019-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2000-00-00 00:00:00.000000 ]
+[ 2010-01-01 00:00:00.000000 ]
#select date_trunc('century', timestamp '2019-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ ????-00-00 00:00:00.000000 ]
+[ 2001-01-01 00:00:00.000000 ]
# 08:07:24 >
# 08:07:24 > "Done."
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
