OmniSciDB  04ee39c94c
DateTruncate.cpp
Go to the documentation of this file.
1 /*
2  * Copyright 2017 MapD Technologies, Inc.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 
17 #include "DateTruncate.h"
18 #include "ExtractFromTime.h"
19 
20 #ifndef __CUDACC__
21 #include <cstdlib> // abort()
22 #endif
23 
24 #include <ctime>
25 #include <iostream>
26 
27 extern "C" NEVER_INLINE DEVICE int64_t create_epoch(int32_t year) {
28  // Note this is not general purpose
29  // it has a final assumption that the year being passed can never be a leap
30  // year
31  // use 2001 epoch time 31 March as start
32 
33  int64_t new_time = kEpochAdjustedDays * kSecsPerDay;
34  bool forward = true;
35  int32_t years_offset = year - kEpochAdjustedYears;
36  // convert year_offset to positive
37  if (years_offset < 0) {
38  forward = false;
39  years_offset = -years_offset;
40  }
41  // now get number of 400 year cycles in the years_offset;
42 
43  int32_t year400 = years_offset / 400;
44  int32_t years_remaining = years_offset - (year400 * 400);
45  int32_t year100 = years_remaining / 100;
46  years_remaining -= year100 * 100;
47  int32_t year4 = years_remaining / 4;
48  years_remaining -= year4 * 4;
49 
50  // get new date I know the final year will never be a leap year
51  if (forward) {
52  new_time += (year400 * kDaysPer400Years + year100 * kDaysPer100Years +
53  year4 * kDaysPer4Years + years_remaining * kDaysPerYear -
55  kSecsPerDay;
56  } else {
57  new_time -= (year400 * kDaysPer400Years + year100 * kDaysPer100Years +
58  year4 * kDaysPer4Years + years_remaining * kDaysPerYear +
59  // one more day for leap year of 2000 when going backward;
61  kSecsPerDay;
62  };
63 
64  return new_time;
65 }
66 
67 /*
68  * @brief support the SQL DATE_TRUNC function
69  */
71  const int64_t timeval) {
72  STATIC_QUAL const int32_t month_lengths[2][kMonsPerYear] = {
73  {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31},
74  {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31}};
75 
76  STATIC_QUAL const uint32_t cumulative_month_epoch_starts[kMonsPerYear] = {0,
77  2678400,
78  5270400,
79  7948800,
80  10540800,
81  13219200,
82  15897600,
83  18489600,
84  21168000,
85  23760000,
86  26438400,
87  29116800};
88  STATIC_QUAL const uint32_t cumulative_quarter_epoch_starts[4] = {
89  0, 7776000, 15638400, 23587200};
90  STATIC_QUAL const uint32_t cumulative_quarter_epoch_starts_leap_year[4] = {
91  0, 7862400, 15724800, 23673600};
92  switch (field) {
93  case dtNANOSECOND:
94  case dtMICROSECOND:
95  case dtMILLISECOND:
96  case dtSECOND:
97  return timeval;
98  case dtMINUTE: {
99  int64_t ret = (timeval / kSecsPerMin) * kSecsPerMin;
100  // in the case of a negative time we still want to push down so need to push
101  // one
102  // more
103  if (ret < 0) {
104  {
105  ret -= kSecsPerMin;
106  }
107  }
108  return ret;
109  }
110  case dtHOUR: {
111  int64_t ret = (timeval / kSecPerHour) * kSecPerHour;
112  // in the case of a negative time we still want to push down so need to push
113  // one
114  // more
115  if (ret < 0) {
116  {
117  ret -= kSecPerHour;
118  }
119  }
120  return ret;
121  }
122  case dtQUARTERDAY: {
123  int64_t ret = (timeval / kSecsPerQuarterDay) * kSecsPerQuarterDay;
124  // in the case of a negative time we still want to push down so need to push
125  // one
126  // more
127  if (ret < 0) {
128  {
129  ret -= kSecsPerQuarterDay;
130  }
131  }
132  return ret;
133  }
134  case dtDAY: {
135  int64_t ret = (timeval / kSecsPerDay) * kSecsPerDay;
136  // in the case of a negative time we still want to push down so need to push
137  // one
138  // more
139  if (ret < 0) {
140  {
141  ret -= kSecsPerDay;
142  }
143  }
144  return ret;
145  }
146  case dtWEEK: {
147  int64_t day = (timeval / kSecsPerDay) * kSecsPerDay;
148  if (day < 0) {
149  {
150  day -= kSecsPerDay;
151  }
152  }
153  int32_t dow = extract_dow(day);
154  return day - (dow * kSecsPerDay);
155  }
156  case dtMONTH: {
157  if (timeval >= 0L && timeval <= UINT32_MAX - (kEpochOffsetYear1900)) {
158  uint32_t seconds_march_1900 = timeval + kEpochOffsetYear1900 - kSecsJanToMar1900;
159  uint32_t seconds_past_4year_period = seconds_march_1900 % kSecondsPer4YearCycle;
160  uint32_t four_year_period_seconds =
161  (seconds_march_1900 / kSecondsPer4YearCycle) * kSecondsPer4YearCycle;
162  uint32_t year_seconds_past_4year_period =
163  (seconds_past_4year_period / kSecondsPerNonLeapYear) * kSecondsPerNonLeapYear;
164  if (seconds_past_4year_period >=
165  kSecondsPer4YearCycle - kUSecsPerDay) { // if we are in Feb 29th
166  year_seconds_past_4year_period -= kSecondsPerNonLeapYear;
167  }
168  uint32_t seconds_past_march =
169  seconds_past_4year_period - year_seconds_past_4year_period;
170  uint32_t month = seconds_past_march /
171  (30 * kUSecsPerDay); // Will make the correct month either be
172  // the guessed month or month before
173  month = month <= 11 ? month : 11;
174  if (cumulative_month_epoch_starts[month] > seconds_past_march) {
175  month--;
176  }
177  return (static_cast<int64_t>(four_year_period_seconds) +
178  year_seconds_past_4year_period + cumulative_month_epoch_starts[month] -
180  }
181  break;
182  }
183  case dtQUARTER: {
184  if (timeval >= 0L && timeval <= UINT32_MAX - kEpochOffsetYear1900) {
185  uint32_t seconds_1900 = timeval + kEpochOffsetYear1900;
186  uint32_t leap_years = (seconds_1900 - kSecsJanToMar1900) / kSecondsPer4YearCycle;
187  uint32_t year =
188  (seconds_1900 - leap_years * kUSecsPerDay) / kSecondsPerNonLeapYear;
189  uint32_t base_year_leap_years = (year - 1) / 4;
190  uint32_t base_year_seconds =
191  year * kSecondsPerNonLeapYear + base_year_leap_years * kUSecsPerDay;
192  bool is_leap_year = year % 4 == 0 && year != 0;
193  const uint32_t* quarter_offsets = is_leap_year
194  ? cumulative_quarter_epoch_starts_leap_year
195  : cumulative_quarter_epoch_starts;
196  uint32_t partial_year_seconds = seconds_1900 % base_year_seconds;
197  uint32_t quarter = partial_year_seconds / (90 * kUSecsPerDay);
198  quarter = quarter <= 3 ? quarter : 3;
199  if (quarter_offsets[quarter] > partial_year_seconds) {
200  quarter--;
201  }
202  return (static_cast<int64_t>(base_year_seconds) + quarter_offsets[quarter] -
204  }
205  break;
206  }
207  case dtYEAR: {
208  if (timeval >= 0L && timeval <= UINT32_MAX - kEpochOffsetYear1900) {
209  uint32_t seconds_1900 = static_cast<uint32_t>(timeval) + kEpochOffsetYear1900;
210  uint32_t leap_years = (seconds_1900 - kSecsJanToMar1900) / kSecondsPer4YearCycle;
211  uint32_t year =
212  (seconds_1900 - leap_years * kUSecsPerDay) / kSecondsPerNonLeapYear;
213  uint32_t base_year_leap_years = (year - 1) / 4;
214  return (static_cast<int64_t>(year) * kSecondsPerNonLeapYear +
215  base_year_leap_years * kUSecsPerDay - kEpochOffsetYear1900);
216  }
217  break;
218  }
219  default:
220  break;
221  }
222 
223  // use ExtractFromTime functions where available
224  // have to do some extra work for these ones
225  tm tm_struct;
226  gmtime_r_newlib(timeval, tm_struct);
227  switch (field) {
228  case dtMONTH: {
229  // clear the time
230  int64_t day = (timeval / kSecsPerDay) * kSecsPerDay;
231  if (day < 0) {
232  {
233  day -= kSecsPerDay;
234  }
235  }
236  // calculate the day of month offset
237  int32_t dom = tm_struct.tm_mday;
238  return (day - (static_cast<int64_t>(dom - 1) * kSecsPerDay));
239  }
240  case dtQUARTER: {
241  // clear the time
242  int64_t day = (timeval / kSecsPerDay) * kSecsPerDay;
243  if (day < 0) {
244  {
245  day -= kSecsPerDay;
246  }
247  }
248  // calculate the day of month offset
249  int32_t dom = tm_struct.tm_mday;
250  // go to the start of the current month
251  day = day - ((dom - 1) * kSecsPerDay);
252  // find what month we are
253  int32_t mon = tm_struct.tm_mon;
254  // find start of quarter
255  int32_t start_of_quarter = tm_struct.tm_mon / 3 * 3;
256  int32_t year = tm_struct.tm_year + kYearBase;
257  // are we in a leap year
258  int32_t leap_year = 0;
259  // only matters if month is March so save some mod operations
260  if (mon == 2) {
261  if (((year % 400) == 0) || ((year % 4) == 0 && ((year % 100) != 0))) {
262  leap_year = 1;
263  }
264  }
265  // now walk back until at correct quarter start
266  for (; mon > start_of_quarter; mon--) {
267  day = day - (month_lengths[0 + leap_year][mon - 1] * kSecsPerDay);
268  }
269  return day;
270  }
271  case dtYEAR: {
272  // clear the time
273  int64_t day = (timeval / kSecsPerDay) * kSecsPerDay;
274  if (day < 0) {
275  {
276  day -= kSecsPerDay;
277  }
278  }
279  // calculate the day of year offset
280  int32_t doy = tm_struct.tm_yday;
281  return day - ((doy)*kSecsPerDay);
282  }
283  case dtDECADE: {
284  int32_t year = tm_struct.tm_year + kYearBase;
285  int32_t decade_start = ((year - 1) / 10) * 10 + 1;
286  return create_epoch(decade_start);
287  }
288  case dtCENTURY: {
289  int32_t year = tm_struct.tm_year + kYearBase;
290  int32_t century_start = ((year - 1) / 100) * 100 + 1;
291  return create_epoch(century_start);
292  }
293  case dtMILLENNIUM: {
294  int32_t year = tm_struct.tm_year + kYearBase;
295  int32_t millennium_start = ((year - 1) / 1000) * 1000 + 1;
296  return create_epoch(millennium_start);
297  }
298  default:
299 #ifdef __CUDACC__
300  return -1;
301 #else
302  abort();
303 #endif
304  }
305 }
306 
308  const int64_t timeval,
309  const int64_t null_val) {
310  if (timeval == null_val) {
311  return null_val;
312  }
313  return DateTruncate(field, timeval);
314 }
315 
316 extern "C" DEVICE int64_t DateTruncateHighPrecisionToDate(const int64_t timeval,
317  const int64_t scale) {
318  const int64_t retval = (timeval / (scale * kSecsPerDay)) * kSecsPerDay;
319  return retval < 0 ? retval - kSecsPerDay : retval;
320 }
321 
322 extern "C" DEVICE int64_t
324  const int64_t scale,
325  const int64_t null_val) {
326  if (timeval == null_val) {
327  return null_val;
328  }
329  return DateTruncateHighPrecisionToDate(timeval, scale);
330 }
331 
332 extern "C" DEVICE int64_t DateTruncateAlterPrecisionScaleUp(const int64_t timeval,
333  const int64_t scale) {
334  return timeval * scale;
335 }
336 
337 extern "C" DEVICE int64_t
339  const int64_t scale,
340  const int64_t null_val) {
341  if (timeval == null_val) {
342  return null_val;
343  }
344  return DateTruncateAlterPrecisionScaleUp(timeval, scale);
345 }
346 
347 extern "C" DEVICE int64_t DateTruncateAlterPrecisionScaleDown(const int64_t timeval,
348  const int64_t scale) {
349  return timeval / scale;
350 }
351 
352 extern "C" DEVICE int64_t
354  const int64_t scale,
355  const int64_t null_val) {
356  if (timeval == null_val) {
357  return null_val;
358  }
359  return DateTruncateAlterPrecisionScaleDown(timeval, scale);
360 }
361 
362 extern "C" DEVICE int64_t DateDiff(const DatetruncField datepart,
363  const int64_t startdate,
364  const int64_t enddate) {
365  int64_t res = enddate - startdate;
366  switch (datepart) {
367  case dtNANOSECOND:
368  return res * kNanoSecsPerSec;
369  case dtMICROSECOND:
370  return res * kMicroSecsPerSec;
371  case dtMILLISECOND:
372  return res * kMilliSecsPerSec;
373  case dtSECOND:
374  return res;
375  case dtMINUTE:
376  return res / kSecsPerMin;
377  case dtHOUR:
378  return res / kSecPerHour;
379  case dtQUARTERDAY:
380  return res / kSecsPerQuarterDay;
381  case dtDAY:
382  return res / kSecsPerDay;
383  case dtWEEK:
384  return res / (kSecsPerDay * kDaysPerWeek);
385  default:
386  break;
387  }
388 
389  auto future_date = (res > 0);
390  auto end = future_date ? enddate : startdate;
391  auto start = future_date ? startdate : enddate;
392  res = 0;
393  int64_t crt = end;
394  while (crt > start) {
395  const int64_t dt = DateTruncate(datepart, crt);
396  if (dt <= start) {
397  break;
398  }
399  ++res;
400  crt = dt - 1;
401  }
402  return future_date ? res : -res;
403 }
404 
405 extern "C" DEVICE int64_t DateDiffHighPrecision(const DatetruncField datepart,
406  const int64_t startdate,
407  const int64_t enddate,
408  const int32_t adj_dimen,
409  const int64_t adj_scale,
410  const int64_t sml_scale,
411  const int64_t scale) {
412  /* TODO(wamsi): When adj_dimen is 1 i.e. both precisions are same,
413  this code is really not required. We cam direcly do enddate-startdate here.
414  Need to address this in refactoring focussed subsequent PR.*/
415  int64_t res = (adj_dimen > 0) ? (enddate - (startdate * adj_scale))
416  : ((enddate * adj_scale) - startdate);
417  switch (datepart) {
418  case dtNANOSECOND:
419  // limit of current granularity
420  return res;
421  case dtMICROSECOND: {
422  if (scale == kNanoSecsPerSec) {
423  return res / kMilliSecsPerSec;
424  } else {
425  { return res; }
426  }
427  }
428  case dtMILLISECOND: {
429  if (scale == kNanoSecsPerSec) {
430  return res / kMicroSecsPerSec;
431  } else if (scale == kMicroSecsPerSec) {
432  return res / kMilliSecsPerSec;
433  } else {
434  { return res; }
435  }
436  }
437  default:
438  break;
439  }
440  const int64_t nstartdate = adj_dimen > 0 ? startdate / sml_scale : startdate / scale;
441  const int64_t nenddate = adj_dimen < 0 ? enddate / sml_scale : enddate / scale;
442  return DateDiff(datepart, nstartdate, nenddate);
443 }
444 
445 extern "C" DEVICE int64_t DateDiffNullable(const DatetruncField datepart,
446  const int64_t startdate,
447  const int64_t enddate,
448  const int64_t null_val) {
449  if (startdate == null_val || enddate == null_val) {
450  return null_val;
451  }
452  return DateDiff(datepart, startdate, enddate);
453 }
454 
455 extern "C" DEVICE int64_t DateDiffHighPrecisionNullable(const DatetruncField datepart,
456  const int64_t startdate,
457  const int64_t enddate,
458  const int32_t adj_dimen,
459  const int64_t adj_scale,
460  const int64_t sml_scale,
461  const int64_t scale,
462  const int64_t null_val) {
463  if (startdate == null_val || enddate == null_val) {
464  return null_val;
465  }
466  return DateDiffHighPrecision(
467  datepart, startdate, enddate, adj_dimen, adj_scale, sml_scale, scale);
468 }
static constexpr int64_t kSecsPerDay
static constexpr uint32_t kSecsJanToMar1900
static constexpr uint32_t kUSecsPerDay
NEVER_INLINE DEVICE int64_t DateTruncate(DatetruncField field, const int64_t timeval)
DEVICE int64_t DateDiffHighPrecisionNullable(const DatetruncField datepart, const int64_t startdate, const int64_t enddate, const int32_t adj_dimen, const int64_t adj_scale, const int64_t sml_scale, const int64_t scale, const int64_t null_val)
static constexpr int32_t kDaysPerYear
static constexpr int64_t kSecsPerMin
static constexpr int64_t kNanoSecsPerSec
static constexpr int64_t kSecsPerQuarterDay
DEVICE int64_t DateDiffNullable(const DatetruncField datepart, const int64_t startdate, const int64_t enddate, const int64_t null_val)
#define STATIC_QUAL
DEVICE int64_t DateTruncateAlterPrecisionScaleUp(const int64_t timeval, const int64_t scale)
DEVICE int64_t DateTruncateNullable(DatetruncField field, const int64_t timeval, const int64_t null_val)
static constexpr int32_t kDaysInJanuary
DEVICE int64_t DateTruncateHighPrecisionToDateNullable(const int64_t timeval, const int64_t scale, const int64_t null_val)
static constexpr uint32_t kEpochOffsetYear1900
static constexpr int32_t kYearBase
#define DEVICE
static constexpr int64_t kMilliSecsPerSec
const rapidjson::Value & field(const rapidjson::Value &obj, const char field[]) noexcept
Definition: JsonAccessors.h:31
DEVICE int32_t extract_dow(const int64_t lcltime)
DEVICE int64_t DateTruncateAlterPrecisionScaleDown(const int64_t timeval, const int64_t scale)
static constexpr int32_t kEpochAdjustedDays
DatetruncField
Definition: DateTruncate.h:42
DEVICE tm gmtime_r_newlib(const int64_t lcltime, tm &res)
DEVICE int64_t DateTruncateAlterPrecisionScaleDownNullable(const int64_t timeval, const int64_t scale, const int64_t null_val)
static constexpr int32_t kDaysPerWeek
static constexpr int32_t kDaysInFebruary
DEVICE int64_t DateDiffHighPrecision(const DatetruncField datepart, const int64_t startdate, const int64_t enddate, const int32_t adj_dimen, const int64_t adj_scale, const int64_t sml_scale, const int64_t scale)
static constexpr int32_t kEpochAdjustedYears
static constexpr uint32_t kSecondsPer4YearCycle
DEVICE int64_t DateTruncateHighPrecisionToDate(const int64_t timeval, const int64_t scale)
static constexpr int64_t kDaysPer400Years
static constexpr int64_t kSecPerHour
DEVICE int64_t DateDiff(const DatetruncField datepart, const int64_t startdate, const int64_t enddate)
#define NEVER_INLINE
static constexpr int32_t kDaysPer4Years
DEVICE int64_t DateTruncateAlterPrecisionScaleUpNullable(const int64_t timeval, const int64_t scale, const int64_t null_val)
static constexpr uint32_t kSecondsPerNonLeapYear
NEVER_INLINE DEVICE int64_t create_epoch(int32_t year)
static constexpr int64_t kMicroSecsPerSec
static constexpr int32_t kMonsPerYear
static constexpr int64_t kDaysPer100Years