1#include "duckdb/common/types/timestamp.hpp"
2
3#include "duckdb/common/exception.hpp"
4#include "duckdb/common/types/date.hpp"
5#include "duckdb/common/types/time.hpp"
6
7#include <iomanip>
8#include <iostream>
9#include <sstream>
10
11using namespace duckdb;
12using namespace std;
13
14constexpr const int32_t MONTHS_PER_YEAR = 12;
15constexpr const int32_t HOURS_PER_DAY = 24; //! assume no daylight savings time changes
16constexpr const int32_t STD_TIMESTAMP_LENGTH = 19;
17constexpr const int32_t START_YEAR = 1900;
18
19constexpr const int32_t SECS_PER_MINUTE = 60;
20constexpr const int32_t MINS_PER_HOUR = 60;
21constexpr const int64_t MSECS_PER_HOUR = 360000;
22constexpr const int64_t MSECS_PER_MINUTE = 60000;
23constexpr const int64_t MSECS_PER_SEC = 1000;
24
25// Used to check amount of days per month in common year and leap year
26constexpr int days_per_month[2][13] = {{31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
27 {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
28constexpr bool isleap(int16_t year) {
29 return (((year) % 4) == 0 && (((year) % 100) != 0 || ((year) % 400) == 0));
30}
31
32// timestamp/datetime uses 64 bits, high 32 bits for date and low 32 bits for time
33// string format is YYYY-MM-DDThh:mm:ssZ
34// T may be a space
35// Z is optional
36// ISO 8601
37
38timestamp_t Timestamp::FromString(string str) {
39 assert(sizeof(timestamp_t) == 8);
40 assert(sizeof(date_t) == 4);
41 assert(sizeof(dtime_t) == 4);
42
43 // In case we have only date we add a default time
44 if (str.size() == 10) {
45 str += " 00:00:00";
46 }
47 // Character length 19 positions minimum to 23 maximum
48 if (str.size() < STD_TIMESTAMP_LENGTH) {
49 throw ConversionException("timestamp field value out of range: \"%s\", "
50 "expected format is (YYYY-MM-DD HH:MM:SS[.MS])",
51 str.c_str());
52 }
53
54 date_t date = Date::FromString(str.substr(0, 10));
55 dtime_t time = Time::FromString(str.substr(10));
56
57 return ((int64_t)date << 32 | (int32_t)time);
58}
59
60string Timestamp::ToString(timestamp_t timestamp) {
61 assert(sizeof(timestamp_t) == 8);
62 assert(sizeof(date_t) == 4);
63 assert(sizeof(dtime_t) == 4);
64
65 return Date::ToString(GetDate(timestamp)) + " " + Time::ToString(GetTime(timestamp));
66}
67
68date_t Timestamp::GetDate(timestamp_t timestamp) {
69 return (date_t)(((int64_t)timestamp) >> 32);
70}
71
72dtime_t Timestamp::GetTime(timestamp_t timestamp) {
73 return (dtime_t)(timestamp & 0xFFFFFFFF);
74}
75
76timestamp_t Timestamp::FromDatetime(date_t date, dtime_t time) {
77 return ((int64_t)date << 32 | (int64_t)time);
78}
79
80void Timestamp::Convert(timestamp_t date, date_t &out_date, dtime_t &out_time) {
81 out_date = GetDate(date);
82 out_time = GetTime(date);
83}
84
85timestamp_t Timestamp::GetCurrentTimestamp() {
86 auto in_time_t = std::time(nullptr);
87 auto utc = std::gmtime(&in_time_t);
88
89 // tm_year[0...] considers the amount of years since 1900 and tm_mon considers the amount of months since january
90 // tm_mon[0-11]
91 auto date = Date::FromDate(utc->tm_year + START_YEAR, utc->tm_mon + 1, utc->tm_mday);
92 auto time = Time::FromTime(utc->tm_hour, utc->tm_min, utc->tm_sec);
93
94 return Timestamp::FromDatetime(date, time);
95}
96
97Interval Timestamp::GetDifference(timestamp_t timestamp_1, timestamp_t timestamp_2) {
98 // First extract the dates
99 auto date1 = GetDate(timestamp_1);
100 auto date2 = GetDate(timestamp_2);
101 // and from date extract the years, months and days
102 int32_t year1, month1, day1;
103 int32_t year2, month2, day2;
104 Date::Convert(date1, year1, month1, day1);
105 Date::Convert(date2, year2, month2, day2);
106 // finally perform the differences
107 auto year_diff = year1 - year2;
108 auto month_diff = month1 - month2;
109 auto day_diff = day1 - day2;
110
111 // Now we extract the time
112 auto time1 = GetTime(timestamp_1);
113 auto time2 = GetTime(timestamp_2);
114
115 // In case time is not specified we do not show it in the output
116 if (time1 == 0) {
117 time2 = time1;
118 }
119
120 // and from time extract hours, minutes, seconds and miliseconds
121 int32_t hour1, min1, sec1, msec1;
122 int32_t hour2, min2, sec2, msec2;
123 Time::Convert(time1, hour1, min1, sec1, msec1);
124 Time::Convert(time2, hour2, min2, sec2, msec2);
125 // finally perform the differences
126 auto hour_diff = hour1 - hour2;
127 auto min_diff = min1 - min2;
128 auto sec_diff = sec1 - sec2;
129 auto msec_diff = msec1 - msec2;
130
131 // flip sign if necessary
132 if (timestamp_1 < timestamp_2) {
133 year_diff = -year_diff;
134 month_diff = -month_diff;
135 day_diff = -day_diff;
136 hour_diff = -hour_diff;
137 min_diff = -min_diff;
138 sec_diff = -sec_diff;
139 msec_diff = -msec_diff;
140 }
141 // now propagate any negative field into the next higher field
142 while (msec_diff < 0) {
143 msec_diff += MSECS_PER_SEC;
144 sec_diff--;
145 }
146 while (sec_diff < 0) {
147 sec_diff += SECS_PER_MINUTE;
148 min_diff--;
149 }
150 while (min_diff < 0) {
151 min_diff += MINS_PER_HOUR;
152 hour_diff--;
153 }
154 while (hour_diff < 0) {
155 hour_diff += HOURS_PER_DAY;
156 day_diff--;
157 }
158 while (day_diff < 0) {
159 if (timestamp_1 < timestamp_2) {
160 day_diff += days_per_month[isleap(year1)][month1 - 1];
161 month_diff--;
162 } else {
163 day_diff += days_per_month[isleap(year2)][month2 - 1];
164 month_diff--;
165 }
166 }
167 while (month_diff < 0) {
168 month_diff += MONTHS_PER_YEAR;
169 year_diff--;
170 }
171
172 // recover sign if necessary
173 if (timestamp_1 < timestamp_2 && (month_diff != 0 || day_diff != 0)) {
174 year_diff = -year_diff;
175 month_diff = -month_diff;
176 day_diff = -day_diff;
177 hour_diff = -hour_diff;
178 min_diff = -min_diff;
179 sec_diff = -sec_diff;
180 msec_diff = -msec_diff;
181 }
182 Interval interval;
183 interval.months = year_diff * MONTHS_PER_YEAR + month_diff;
184 interval.days = day_diff;
185 interval.time =
186 ((((((hour_diff * MINS_PER_HOUR) + min_diff) * SECS_PER_MINUTE) + sec_diff) * MSECS_PER_SEC) + msec_diff);
187
188 return interval;
189}
190
191timestamp_struct Timestamp::IntervalToTimestamp(Interval &interval) {
192 timestamp_struct timestamp;
193
194 if (interval.months != 0) {
195 timestamp.year = interval.months / MONTHS_PER_YEAR;
196 timestamp.month = interval.months % MONTHS_PER_YEAR;
197
198 } else {
199 timestamp.year = 0;
200 timestamp.month = 0;
201 }
202 timestamp.day = interval.days;
203 auto time = interval.time;
204
205 timestamp.hour = time / MSECS_PER_HOUR;
206 time -= timestamp.hour * MSECS_PER_HOUR;
207 timestamp.min = time / MSECS_PER_MINUTE;
208 time -= timestamp.min * MSECS_PER_MINUTE;
209 timestamp.sec = time / MSECS_PER_SEC;
210 timestamp.msec = time - (timestamp.sec * MSECS_PER_SEC);
211 return timestamp;
212}
213
214Interval TimestampToInterval(timestamp_struct *timestamp) {
215 Interval interval;
216
217 interval.months = timestamp->year * MONTHS_PER_YEAR + timestamp->month;
218 interval.days = timestamp->day;
219 interval.time =
220 ((((((timestamp->hour * MINS_PER_HOUR) + timestamp->min) * SECS_PER_MINUTE) + timestamp->sec) * MSECS_PER_SEC) +
221 timestamp->msec);
222
223 return interval;
224}
225
226int64_t Timestamp::GetEpoch(timestamp_t timestamp) {
227 return Date::Epoch(Timestamp::GetDate(timestamp)) + (int64_t)(Timestamp::GetTime(timestamp) / 1000);
228}
229
230int64_t Timestamp::GetMilliseconds(timestamp_t timestamp) {
231 int n = Timestamp::GetTime(timestamp);
232 int m = n / 60000;
233 return n - m * 60000;
234}
235
236int64_t Timestamp::GetSeconds(timestamp_t timestamp) {
237 int n = Timestamp::GetTime(timestamp);
238 int m = n / 60000;
239 return (n - m * 60000) / 1000;
240}
241
242int64_t Timestamp::GetMinutes(timestamp_t timestamp) {
243 int n = Timestamp::GetTime(timestamp);
244 int h = n / 3600000;
245 return (n - h * 3600000) / 60000;
246}
247
248int64_t Timestamp::GetHours(timestamp_t timestamp) {
249 return Timestamp::GetTime(timestamp) / 3600000;
250}
251