1 /*-------------------------------------------------------------------------
2 *
3 * timestamp.c
4 * Functions for the built-in SQL types "timestamp" and "interval".
5 *
6 * Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
7 * Portions Copyright (c) 1994, Regents of the University of California
8 *
9 *
10 * IDENTIFICATION
11 * src/backend/utils/adt/timestamp.c
12 *
13 *-------------------------------------------------------------------------
14 */
15
16 #include "postgres.h"
17
18 #include <ctype.h>
19 #include <math.h>
20 #include <limits.h>
21 #include <sys/time.h>
22
23 #include "access/xact.h"
24 #include "catalog/pg_type.h"
25 #include "common/int128.h"
26 #include "funcapi.h"
27 #include "libpq/pqformat.h"
28 #include "miscadmin.h"
29 #include "nodes/makefuncs.h"
30 #include "nodes/nodeFuncs.h"
31 #include "nodes/supportnodes.h"
32 #include "parser/scansup.h"
33 #include "utils/array.h"
34 #include "utils/builtins.h"
35 #include "utils/date.h"
36 #include "utils/datetime.h"
37 #include "utils/float.h"
38
39 /*
40 * gcc's -ffast-math switch breaks routines that expect exact results from
41 * expressions like timeval / SECS_PER_HOUR, where timeval is double.
42 */
43 #ifdef __FAST_MATH__
44 #error -ffast-math is known to break this code
45 #endif
46
47 #define SAMESIGN(a,b) (((a) < 0) == ((b) < 0))
48
49 /* Set at postmaster start */
50 TimestampTz PgStartTime;
51
52 /* Set at configuration reload */
53 TimestampTz PgReloadTime;
54
55 typedef struct
56 {
57 Timestamp current;
58 Timestamp finish;
59 Interval step;
60 int step_sign;
61 } generate_series_timestamp_fctx;
62
63 typedef struct
64 {
65 TimestampTz current;
66 TimestampTz finish;
67 Interval step;
68 int step_sign;
69 } generate_series_timestamptz_fctx;
70
71
72 static TimeOffset time2t(const int hour, const int min, const int sec, const fsec_t fsec);
73 static Timestamp dt2local(Timestamp dt, int timezone);
74 static void AdjustTimestampForTypmod(Timestamp *time, int32 typmod);
75 static void AdjustIntervalForTypmod(Interval *interval, int32 typmod);
76 static TimestampTz timestamp2timestamptz(Timestamp timestamp);
77 static Timestamp timestamptz2timestamp(TimestampTz timestamp);
78
79
80 /* common code for timestamptypmodin and timestamptztypmodin */
81 static int32
anytimestamp_typmodin(bool istz,ArrayType * ta)82 anytimestamp_typmodin(bool istz, ArrayType *ta)
83 {
84 int32 *tl;
85 int n;
86
87 tl = ArrayGetIntegerTypmods(ta, &n);
88
89 /*
90 * we're not too tense about good error message here because grammar
91 * shouldn't allow wrong number of modifiers for TIMESTAMP
92 */
93 if (n != 1)
94 ereport(ERROR,
95 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
96 errmsg("invalid type modifier")));
97
98 return anytimestamp_typmod_check(istz, tl[0]);
99 }
100
101 /* exported so parse_expr.c can use it */
102 int32
anytimestamp_typmod_check(bool istz,int32 typmod)103 anytimestamp_typmod_check(bool istz, int32 typmod)
104 {
105 if (typmod < 0)
106 ereport(ERROR,
107 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
108 errmsg("TIMESTAMP(%d)%s precision must not be negative",
109 typmod, (istz ? " WITH TIME ZONE" : ""))));
110 if (typmod > MAX_TIMESTAMP_PRECISION)
111 {
112 ereport(WARNING,
113 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
114 errmsg("TIMESTAMP(%d)%s precision reduced to maximum allowed, %d",
115 typmod, (istz ? " WITH TIME ZONE" : ""),
116 MAX_TIMESTAMP_PRECISION)));
117 typmod = MAX_TIMESTAMP_PRECISION;
118 }
119
120 return typmod;
121 }
122
123 /* common code for timestamptypmodout and timestamptztypmodout */
124 static char *
anytimestamp_typmodout(bool istz,int32 typmod)125 anytimestamp_typmodout(bool istz, int32 typmod)
126 {
127 const char *tz = istz ? " with time zone" : " without time zone";
128
129 if (typmod >= 0)
130 return psprintf("(%d)%s", (int) typmod, tz);
131 else
132 return psprintf("%s", tz);
133 }
134
135
136 /*****************************************************************************
137 * USER I/O ROUTINES *
138 *****************************************************************************/
139
140 /* timestamp_in()
141 * Convert a string to internal form.
142 */
143 Datum
timestamp_in(PG_FUNCTION_ARGS)144 timestamp_in(PG_FUNCTION_ARGS)
145 {
146 char *str = PG_GETARG_CSTRING(0);
147
148 #ifdef NOT_USED
149 Oid typelem = PG_GETARG_OID(1);
150 #endif
151 int32 typmod = PG_GETARG_INT32(2);
152 Timestamp result;
153 fsec_t fsec;
154 struct pg_tm tt,
155 *tm = &tt;
156 int tz;
157 int dtype;
158 int nf;
159 int dterr;
160 char *field[MAXDATEFIELDS];
161 int ftype[MAXDATEFIELDS];
162 char workbuf[MAXDATELEN + MAXDATEFIELDS];
163
164 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
165 field, ftype, MAXDATEFIELDS, &nf);
166 if (dterr == 0)
167 dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
168 if (dterr != 0)
169 DateTimeParseError(dterr, str, "timestamp");
170
171 switch (dtype)
172 {
173 case DTK_DATE:
174 if (tm2timestamp(tm, fsec, NULL, &result) != 0)
175 ereport(ERROR,
176 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
177 errmsg("timestamp out of range: \"%s\"", str)));
178 break;
179
180 case DTK_EPOCH:
181 result = SetEpochTimestamp();
182 break;
183
184 case DTK_LATE:
185 TIMESTAMP_NOEND(result);
186 break;
187
188 case DTK_EARLY:
189 TIMESTAMP_NOBEGIN(result);
190 break;
191
192 default:
193 elog(ERROR, "unexpected dtype %d while parsing timestamp \"%s\"",
194 dtype, str);
195 TIMESTAMP_NOEND(result);
196 }
197
198 AdjustTimestampForTypmod(&result, typmod);
199
200 PG_RETURN_TIMESTAMP(result);
201 }
202
203 /* timestamp_out()
204 * Convert a timestamp to external form.
205 */
206 Datum
timestamp_out(PG_FUNCTION_ARGS)207 timestamp_out(PG_FUNCTION_ARGS)
208 {
209 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
210 char *result;
211 struct pg_tm tt,
212 *tm = &tt;
213 fsec_t fsec;
214 char buf[MAXDATELEN + 1];
215
216 if (TIMESTAMP_NOT_FINITE(timestamp))
217 EncodeSpecialTimestamp(timestamp, buf);
218 else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
219 EncodeDateTime(tm, fsec, false, 0, NULL, DateStyle, buf);
220 else
221 ereport(ERROR,
222 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
223 errmsg("timestamp out of range")));
224
225 result = pstrdup(buf);
226 PG_RETURN_CSTRING(result);
227 }
228
229 /*
230 * timestamp_recv - converts external binary format to timestamp
231 */
232 Datum
timestamp_recv(PG_FUNCTION_ARGS)233 timestamp_recv(PG_FUNCTION_ARGS)
234 {
235 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
236
237 #ifdef NOT_USED
238 Oid typelem = PG_GETARG_OID(1);
239 #endif
240 int32 typmod = PG_GETARG_INT32(2);
241 Timestamp timestamp;
242 struct pg_tm tt,
243 *tm = &tt;
244 fsec_t fsec;
245
246 timestamp = (Timestamp) pq_getmsgint64(buf);
247
248 /* range check: see if timestamp_out would like it */
249 if (TIMESTAMP_NOT_FINITE(timestamp))
250 /* ok */ ;
251 else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0 ||
252 !IS_VALID_TIMESTAMP(timestamp))
253 ereport(ERROR,
254 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
255 errmsg("timestamp out of range")));
256
257 AdjustTimestampForTypmod(×tamp, typmod);
258
259 PG_RETURN_TIMESTAMP(timestamp);
260 }
261
262 /*
263 * timestamp_send - converts timestamp to binary format
264 */
265 Datum
timestamp_send(PG_FUNCTION_ARGS)266 timestamp_send(PG_FUNCTION_ARGS)
267 {
268 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
269 StringInfoData buf;
270
271 pq_begintypsend(&buf);
272 pq_sendint64(&buf, timestamp);
273 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
274 }
275
276 Datum
timestamptypmodin(PG_FUNCTION_ARGS)277 timestamptypmodin(PG_FUNCTION_ARGS)
278 {
279 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
280
281 PG_RETURN_INT32(anytimestamp_typmodin(false, ta));
282 }
283
284 Datum
timestamptypmodout(PG_FUNCTION_ARGS)285 timestamptypmodout(PG_FUNCTION_ARGS)
286 {
287 int32 typmod = PG_GETARG_INT32(0);
288
289 PG_RETURN_CSTRING(anytimestamp_typmodout(false, typmod));
290 }
291
292
293 /*
294 * timestamp_support()
295 *
296 * Planner support function for the timestamp_scale() and timestamptz_scale()
297 * length coercion functions (we need not distinguish them here).
298 */
299 Datum
timestamp_support(PG_FUNCTION_ARGS)300 timestamp_support(PG_FUNCTION_ARGS)
301 {
302 Node *rawreq = (Node *) PG_GETARG_POINTER(0);
303 Node *ret = NULL;
304
305 if (IsA(rawreq, SupportRequestSimplify))
306 {
307 SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
308
309 ret = TemporalSimplify(MAX_TIMESTAMP_PRECISION, (Node *) req->fcall);
310 }
311
312 PG_RETURN_POINTER(ret);
313 }
314
315 /* timestamp_scale()
316 * Adjust time type for specified scale factor.
317 * Used by PostgreSQL type system to stuff columns.
318 */
319 Datum
timestamp_scale(PG_FUNCTION_ARGS)320 timestamp_scale(PG_FUNCTION_ARGS)
321 {
322 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
323 int32 typmod = PG_GETARG_INT32(1);
324 Timestamp result;
325
326 result = timestamp;
327
328 AdjustTimestampForTypmod(&result, typmod);
329
330 PG_RETURN_TIMESTAMP(result);
331 }
332
333 /*
334 * AdjustTimestampForTypmod --- round off a timestamp to suit given typmod
335 * Works for either timestamp or timestamptz.
336 */
337 static void
AdjustTimestampForTypmod(Timestamp * time,int32 typmod)338 AdjustTimestampForTypmod(Timestamp *time, int32 typmod)
339 {
340 static const int64 TimestampScales[MAX_TIMESTAMP_PRECISION + 1] = {
341 INT64CONST(1000000),
342 INT64CONST(100000),
343 INT64CONST(10000),
344 INT64CONST(1000),
345 INT64CONST(100),
346 INT64CONST(10),
347 INT64CONST(1)
348 };
349
350 static const int64 TimestampOffsets[MAX_TIMESTAMP_PRECISION + 1] = {
351 INT64CONST(500000),
352 INT64CONST(50000),
353 INT64CONST(5000),
354 INT64CONST(500),
355 INT64CONST(50),
356 INT64CONST(5),
357 INT64CONST(0)
358 };
359
360 if (!TIMESTAMP_NOT_FINITE(*time)
361 && (typmod != -1) && (typmod != MAX_TIMESTAMP_PRECISION))
362 {
363 if (typmod < 0 || typmod > MAX_TIMESTAMP_PRECISION)
364 ereport(ERROR,
365 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
366 errmsg("timestamp(%d) precision must be between %d and %d",
367 typmod, 0, MAX_TIMESTAMP_PRECISION)));
368
369 if (*time >= INT64CONST(0))
370 {
371 *time = ((*time + TimestampOffsets[typmod]) / TimestampScales[typmod]) *
372 TimestampScales[typmod];
373 }
374 else
375 {
376 *time = -((((-*time) + TimestampOffsets[typmod]) / TimestampScales[typmod])
377 * TimestampScales[typmod]);
378 }
379 }
380 }
381
382
383 /* timestamptz_in()
384 * Convert a string to internal form.
385 */
386 Datum
timestamptz_in(PG_FUNCTION_ARGS)387 timestamptz_in(PG_FUNCTION_ARGS)
388 {
389 char *str = PG_GETARG_CSTRING(0);
390
391 #ifdef NOT_USED
392 Oid typelem = PG_GETARG_OID(1);
393 #endif
394 int32 typmod = PG_GETARG_INT32(2);
395 TimestampTz result;
396 fsec_t fsec;
397 struct pg_tm tt,
398 *tm = &tt;
399 int tz;
400 int dtype;
401 int nf;
402 int dterr;
403 char *field[MAXDATEFIELDS];
404 int ftype[MAXDATEFIELDS];
405 char workbuf[MAXDATELEN + MAXDATEFIELDS];
406
407 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
408 field, ftype, MAXDATEFIELDS, &nf);
409 if (dterr == 0)
410 dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
411 if (dterr != 0)
412 DateTimeParseError(dterr, str, "timestamp with time zone");
413
414 switch (dtype)
415 {
416 case DTK_DATE:
417 if (tm2timestamp(tm, fsec, &tz, &result) != 0)
418 ereport(ERROR,
419 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
420 errmsg("timestamp out of range: \"%s\"", str)));
421 break;
422
423 case DTK_EPOCH:
424 result = SetEpochTimestamp();
425 break;
426
427 case DTK_LATE:
428 TIMESTAMP_NOEND(result);
429 break;
430
431 case DTK_EARLY:
432 TIMESTAMP_NOBEGIN(result);
433 break;
434
435 default:
436 elog(ERROR, "unexpected dtype %d while parsing timestamptz \"%s\"",
437 dtype, str);
438 TIMESTAMP_NOEND(result);
439 }
440
441 AdjustTimestampForTypmod(&result, typmod);
442
443 PG_RETURN_TIMESTAMPTZ(result);
444 }
445
446 /*
447 * Try to parse a timezone specification, and return its timezone offset value
448 * if it's acceptable. Otherwise, an error is thrown.
449 *
450 * Note: some code paths update tm->tm_isdst, and some don't; current callers
451 * don't care, so we don't bother being consistent.
452 */
453 static int
parse_sane_timezone(struct pg_tm * tm,text * zone)454 parse_sane_timezone(struct pg_tm *tm, text *zone)
455 {
456 char tzname[TZ_STRLEN_MAX + 1];
457 int rt;
458 int tz;
459
460 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
461
462 /*
463 * Look up the requested timezone. First we try to interpret it as a
464 * numeric timezone specification; if DecodeTimezone decides it doesn't
465 * like the format, we look in the timezone abbreviation table (to handle
466 * cases like "EST"), and if that also fails, we look in the timezone
467 * database (to handle cases like "America/New_York"). (This matches the
468 * order in which timestamp input checks the cases; it's important because
469 * the timezone database unwisely uses a few zone names that are identical
470 * to offset abbreviations.)
471 *
472 * Note pg_tzset happily parses numeric input that DecodeTimezone would
473 * reject. To avoid having it accept input that would otherwise be seen
474 * as invalid, it's enough to disallow having a digit in the first
475 * position of our input string.
476 */
477 if (isdigit((unsigned char) *tzname))
478 ereport(ERROR,
479 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
480 errmsg("invalid input syntax for type %s: \"%s\"",
481 "numeric time zone", tzname),
482 errhint("Numeric time zones must have \"-\" or \"+\" as first character.")));
483
484 rt = DecodeTimezone(tzname, &tz);
485 if (rt != 0)
486 {
487 char *lowzone;
488 int type,
489 val;
490 pg_tz *tzp;
491
492 if (rt == DTERR_TZDISP_OVERFLOW)
493 ereport(ERROR,
494 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
495 errmsg("numeric time zone \"%s\" out of range", tzname)));
496 else if (rt != DTERR_BAD_FORMAT)
497 ereport(ERROR,
498 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
499 errmsg("time zone \"%s\" not recognized", tzname)));
500
501 /* DecodeTimezoneAbbrev requires lowercase input */
502 lowzone = downcase_truncate_identifier(tzname,
503 strlen(tzname),
504 false);
505 type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
506
507 if (type == TZ || type == DTZ)
508 {
509 /* fixed-offset abbreviation */
510 tz = -val;
511 }
512 else if (type == DYNTZ)
513 {
514 /* dynamic-offset abbreviation, resolve using specified time */
515 tz = DetermineTimeZoneAbbrevOffset(tm, tzname, tzp);
516 }
517 else
518 {
519 /* try it as a full zone name */
520 tzp = pg_tzset(tzname);
521 if (tzp)
522 tz = DetermineTimeZoneOffset(tm, tzp);
523 else
524 ereport(ERROR,
525 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
526 errmsg("time zone \"%s\" not recognized", tzname)));
527 }
528 }
529
530 return tz;
531 }
532
533 /*
534 * make_timestamp_internal
535 * workhorse for make_timestamp and make_timestamptz
536 */
537 static Timestamp
make_timestamp_internal(int year,int month,int day,int hour,int min,double sec)538 make_timestamp_internal(int year, int month, int day,
539 int hour, int min, double sec)
540 {
541 struct pg_tm tm;
542 TimeOffset date;
543 TimeOffset time;
544 int dterr;
545 Timestamp result;
546
547 tm.tm_year = year;
548 tm.tm_mon = month;
549 tm.tm_mday = day;
550
551 /*
552 * Note: we'll reject zero or negative year values. Perhaps negatives
553 * should be allowed to represent BC years?
554 */
555 dterr = ValidateDate(DTK_DATE_M, false, false, false, &tm);
556
557 if (dterr != 0)
558 ereport(ERROR,
559 (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
560 errmsg("date field value out of range: %d-%02d-%02d",
561 year, month, day)));
562
563 if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
564 ereport(ERROR,
565 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
566 errmsg("date out of range: %d-%02d-%02d",
567 year, month, day)));
568
569 date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
570
571 /* Check for time overflow */
572 if (float_time_overflows(hour, min, sec))
573 ereport(ERROR,
574 (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
575 errmsg("time field value out of range: %d:%02d:%02g",
576 hour, min, sec)));
577
578 /* This should match tm2time */
579 time = (((hour * MINS_PER_HOUR + min) * SECS_PER_MINUTE)
580 * USECS_PER_SEC) + (int64) rint(sec * USECS_PER_SEC);
581
582 result = date * USECS_PER_DAY + time;
583 /* check for major overflow */
584 if ((result - time) / USECS_PER_DAY != date)
585 ereport(ERROR,
586 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
587 errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
588 year, month, day,
589 hour, min, sec)));
590
591 /* check for just-barely overflow (okay except time-of-day wraps) */
592 /* caution: we want to allow 1999-12-31 24:00:00 */
593 if ((result < 0 && date > 0) ||
594 (result > 0 && date < -1))
595 ereport(ERROR,
596 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
597 errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
598 year, month, day,
599 hour, min, sec)));
600
601 /* final range check catches just-out-of-range timestamps */
602 if (!IS_VALID_TIMESTAMP(result))
603 ereport(ERROR,
604 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
605 errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
606 year, month, day,
607 hour, min, sec)));
608
609 return result;
610 }
611
612 /*
613 * make_timestamp() - timestamp constructor
614 */
615 Datum
make_timestamp(PG_FUNCTION_ARGS)616 make_timestamp(PG_FUNCTION_ARGS)
617 {
618 int32 year = PG_GETARG_INT32(0);
619 int32 month = PG_GETARG_INT32(1);
620 int32 mday = PG_GETARG_INT32(2);
621 int32 hour = PG_GETARG_INT32(3);
622 int32 min = PG_GETARG_INT32(4);
623 float8 sec = PG_GETARG_FLOAT8(5);
624 Timestamp result;
625
626 result = make_timestamp_internal(year, month, mday,
627 hour, min, sec);
628
629 PG_RETURN_TIMESTAMP(result);
630 }
631
632 /*
633 * make_timestamptz() - timestamp with time zone constructor
634 */
635 Datum
make_timestamptz(PG_FUNCTION_ARGS)636 make_timestamptz(PG_FUNCTION_ARGS)
637 {
638 int32 year = PG_GETARG_INT32(0);
639 int32 month = PG_GETARG_INT32(1);
640 int32 mday = PG_GETARG_INT32(2);
641 int32 hour = PG_GETARG_INT32(3);
642 int32 min = PG_GETARG_INT32(4);
643 float8 sec = PG_GETARG_FLOAT8(5);
644 Timestamp result;
645
646 result = make_timestamp_internal(year, month, mday,
647 hour, min, sec);
648
649 PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(result));
650 }
651
652 /*
653 * Construct a timestamp with time zone.
654 * As above, but the time zone is specified as seventh argument.
655 */
656 Datum
make_timestamptz_at_timezone(PG_FUNCTION_ARGS)657 make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
658 {
659 int32 year = PG_GETARG_INT32(0);
660 int32 month = PG_GETARG_INT32(1);
661 int32 mday = PG_GETARG_INT32(2);
662 int32 hour = PG_GETARG_INT32(3);
663 int32 min = PG_GETARG_INT32(4);
664 float8 sec = PG_GETARG_FLOAT8(5);
665 text *zone = PG_GETARG_TEXT_PP(6);
666 TimestampTz result;
667 Timestamp timestamp;
668 struct pg_tm tt;
669 int tz;
670 fsec_t fsec;
671
672 timestamp = make_timestamp_internal(year, month, mday,
673 hour, min, sec);
674
675 if (timestamp2tm(timestamp, NULL, &tt, &fsec, NULL, NULL) != 0)
676 ereport(ERROR,
677 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
678 errmsg("timestamp out of range")));
679
680 tz = parse_sane_timezone(&tt, zone);
681
682 result = dt2local(timestamp, -tz);
683
684 if (!IS_VALID_TIMESTAMP(result))
685 ereport(ERROR,
686 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
687 errmsg("timestamp out of range")));
688
689 PG_RETURN_TIMESTAMPTZ(result);
690 }
691
692 /*
693 * to_timestamp(double precision)
694 * Convert UNIX epoch to timestamptz.
695 */
696 Datum
float8_timestamptz(PG_FUNCTION_ARGS)697 float8_timestamptz(PG_FUNCTION_ARGS)
698 {
699 float8 seconds = PG_GETARG_FLOAT8(0);
700 TimestampTz result;
701
702 /* Deal with NaN and infinite inputs ... */
703 if (isnan(seconds))
704 ereport(ERROR,
705 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
706 errmsg("timestamp cannot be NaN")));
707
708 if (isinf(seconds))
709 {
710 if (seconds < 0)
711 TIMESTAMP_NOBEGIN(result);
712 else
713 TIMESTAMP_NOEND(result);
714 }
715 else
716 {
717 /* Out of range? */
718 if (seconds <
719 (float8) SECS_PER_DAY * (DATETIME_MIN_JULIAN - UNIX_EPOCH_JDATE)
720 || seconds >=
721 (float8) SECS_PER_DAY * (TIMESTAMP_END_JULIAN - UNIX_EPOCH_JDATE))
722 ereport(ERROR,
723 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
724 errmsg("timestamp out of range: \"%g\"", seconds)));
725
726 /* Convert UNIX epoch to Postgres epoch */
727 seconds -= ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
728
729 seconds = rint(seconds * USECS_PER_SEC);
730 result = (int64) seconds;
731
732 /* Recheck in case roundoff produces something just out of range */
733 if (!IS_VALID_TIMESTAMP(result))
734 ereport(ERROR,
735 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
736 errmsg("timestamp out of range: \"%g\"",
737 PG_GETARG_FLOAT8(0))));
738 }
739
740 PG_RETURN_TIMESTAMP(result);
741 }
742
743 /* timestamptz_out()
744 * Convert a timestamp to external form.
745 */
746 Datum
timestamptz_out(PG_FUNCTION_ARGS)747 timestamptz_out(PG_FUNCTION_ARGS)
748 {
749 TimestampTz dt = PG_GETARG_TIMESTAMPTZ(0);
750 char *result;
751 int tz;
752 struct pg_tm tt,
753 *tm = &tt;
754 fsec_t fsec;
755 const char *tzn;
756 char buf[MAXDATELEN + 1];
757
758 if (TIMESTAMP_NOT_FINITE(dt))
759 EncodeSpecialTimestamp(dt, buf);
760 else if (timestamp2tm(dt, &tz, tm, &fsec, &tzn, NULL) == 0)
761 EncodeDateTime(tm, fsec, true, tz, tzn, DateStyle, buf);
762 else
763 ereport(ERROR,
764 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
765 errmsg("timestamp out of range")));
766
767 result = pstrdup(buf);
768 PG_RETURN_CSTRING(result);
769 }
770
771 /*
772 * timestamptz_recv - converts external binary format to timestamptz
773 */
774 Datum
timestamptz_recv(PG_FUNCTION_ARGS)775 timestamptz_recv(PG_FUNCTION_ARGS)
776 {
777 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
778
779 #ifdef NOT_USED
780 Oid typelem = PG_GETARG_OID(1);
781 #endif
782 int32 typmod = PG_GETARG_INT32(2);
783 TimestampTz timestamp;
784 int tz;
785 struct pg_tm tt,
786 *tm = &tt;
787 fsec_t fsec;
788
789 timestamp = (TimestampTz) pq_getmsgint64(buf);
790
791 /* range check: see if timestamptz_out would like it */
792 if (TIMESTAMP_NOT_FINITE(timestamp))
793 /* ok */ ;
794 else if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0 ||
795 !IS_VALID_TIMESTAMP(timestamp))
796 ereport(ERROR,
797 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
798 errmsg("timestamp out of range")));
799
800 AdjustTimestampForTypmod(×tamp, typmod);
801
802 PG_RETURN_TIMESTAMPTZ(timestamp);
803 }
804
805 /*
806 * timestamptz_send - converts timestamptz to binary format
807 */
808 Datum
timestamptz_send(PG_FUNCTION_ARGS)809 timestamptz_send(PG_FUNCTION_ARGS)
810 {
811 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
812 StringInfoData buf;
813
814 pq_begintypsend(&buf);
815 pq_sendint64(&buf, timestamp);
816 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
817 }
818
819 Datum
timestamptztypmodin(PG_FUNCTION_ARGS)820 timestamptztypmodin(PG_FUNCTION_ARGS)
821 {
822 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
823
824 PG_RETURN_INT32(anytimestamp_typmodin(true, ta));
825 }
826
827 Datum
timestamptztypmodout(PG_FUNCTION_ARGS)828 timestamptztypmodout(PG_FUNCTION_ARGS)
829 {
830 int32 typmod = PG_GETARG_INT32(0);
831
832 PG_RETURN_CSTRING(anytimestamp_typmodout(true, typmod));
833 }
834
835
836 /* timestamptz_scale()
837 * Adjust time type for specified scale factor.
838 * Used by PostgreSQL type system to stuff columns.
839 */
840 Datum
timestamptz_scale(PG_FUNCTION_ARGS)841 timestamptz_scale(PG_FUNCTION_ARGS)
842 {
843 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
844 int32 typmod = PG_GETARG_INT32(1);
845 TimestampTz result;
846
847 result = timestamp;
848
849 AdjustTimestampForTypmod(&result, typmod);
850
851 PG_RETURN_TIMESTAMPTZ(result);
852 }
853
854
855 /* interval_in()
856 * Convert a string to internal form.
857 *
858 * External format(s):
859 * Uses the generic date/time parsing and decoding routines.
860 */
861 Datum
interval_in(PG_FUNCTION_ARGS)862 interval_in(PG_FUNCTION_ARGS)
863 {
864 char *str = PG_GETARG_CSTRING(0);
865
866 #ifdef NOT_USED
867 Oid typelem = PG_GETARG_OID(1);
868 #endif
869 int32 typmod = PG_GETARG_INT32(2);
870 Interval *result;
871 fsec_t fsec;
872 struct pg_tm tt,
873 *tm = &tt;
874 int dtype;
875 int nf;
876 int range;
877 int dterr;
878 char *field[MAXDATEFIELDS];
879 int ftype[MAXDATEFIELDS];
880 char workbuf[256];
881
882 tm->tm_year = 0;
883 tm->tm_mon = 0;
884 tm->tm_mday = 0;
885 tm->tm_hour = 0;
886 tm->tm_min = 0;
887 tm->tm_sec = 0;
888 fsec = 0;
889
890 if (typmod >= 0)
891 range = INTERVAL_RANGE(typmod);
892 else
893 range = INTERVAL_FULL_RANGE;
894
895 dterr = ParseDateTime(str, workbuf, sizeof(workbuf), field,
896 ftype, MAXDATEFIELDS, &nf);
897 if (dterr == 0)
898 dterr = DecodeInterval(field, ftype, nf, range,
899 &dtype, tm, &fsec);
900
901 /* if those functions think it's a bad format, try ISO8601 style */
902 if (dterr == DTERR_BAD_FORMAT)
903 dterr = DecodeISO8601Interval(str,
904 &dtype, tm, &fsec);
905
906 if (dterr != 0)
907 {
908 if (dterr == DTERR_FIELD_OVERFLOW)
909 dterr = DTERR_INTERVAL_OVERFLOW;
910 DateTimeParseError(dterr, str, "interval");
911 }
912
913 result = (Interval *) palloc(sizeof(Interval));
914
915 switch (dtype)
916 {
917 case DTK_DELTA:
918 if (tm2interval(tm, fsec, result) != 0)
919 ereport(ERROR,
920 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
921 errmsg("interval out of range")));
922 break;
923
924 default:
925 elog(ERROR, "unexpected dtype %d while parsing interval \"%s\"",
926 dtype, str);
927 }
928
929 AdjustIntervalForTypmod(result, typmod);
930
931 PG_RETURN_INTERVAL_P(result);
932 }
933
934 /* interval_out()
935 * Convert a time span to external form.
936 */
937 Datum
interval_out(PG_FUNCTION_ARGS)938 interval_out(PG_FUNCTION_ARGS)
939 {
940 Interval *span = PG_GETARG_INTERVAL_P(0);
941 char *result;
942 struct pg_tm tt,
943 *tm = &tt;
944 fsec_t fsec;
945 char buf[MAXDATELEN + 1];
946
947 if (interval2tm(*span, tm, &fsec) != 0)
948 elog(ERROR, "could not convert interval to tm");
949
950 EncodeInterval(tm, fsec, IntervalStyle, buf);
951
952 result = pstrdup(buf);
953 PG_RETURN_CSTRING(result);
954 }
955
956 /*
957 * interval_recv - converts external binary format to interval
958 */
959 Datum
interval_recv(PG_FUNCTION_ARGS)960 interval_recv(PG_FUNCTION_ARGS)
961 {
962 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
963
964 #ifdef NOT_USED
965 Oid typelem = PG_GETARG_OID(1);
966 #endif
967 int32 typmod = PG_GETARG_INT32(2);
968 Interval *interval;
969
970 interval = (Interval *) palloc(sizeof(Interval));
971
972 interval->time = pq_getmsgint64(buf);
973 interval->day = pq_getmsgint(buf, sizeof(interval->day));
974 interval->month = pq_getmsgint(buf, sizeof(interval->month));
975
976 AdjustIntervalForTypmod(interval, typmod);
977
978 PG_RETURN_INTERVAL_P(interval);
979 }
980
981 /*
982 * interval_send - converts interval to binary format
983 */
984 Datum
interval_send(PG_FUNCTION_ARGS)985 interval_send(PG_FUNCTION_ARGS)
986 {
987 Interval *interval = PG_GETARG_INTERVAL_P(0);
988 StringInfoData buf;
989
990 pq_begintypsend(&buf);
991 pq_sendint64(&buf, interval->time);
992 pq_sendint32(&buf, interval->day);
993 pq_sendint32(&buf, interval->month);
994 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
995 }
996
997 /*
998 * The interval typmod stores a "range" in its high 16 bits and a "precision"
999 * in its low 16 bits. Both contribute to defining the resolution of the
1000 * type. Range addresses resolution granules larger than one second, and
1001 * precision specifies resolution below one second. This representation can
1002 * express all SQL standard resolutions, but we implement them all in terms of
1003 * truncating rightward from some position. Range is a bitmap of permitted
1004 * fields, but only the temporally-smallest such field is significant to our
1005 * calculations. Precision is a count of sub-second decimal places to retain.
1006 * Setting all bits (INTERVAL_FULL_PRECISION) gives the same truncation
1007 * semantics as choosing MAX_INTERVAL_PRECISION.
1008 */
1009 Datum
intervaltypmodin(PG_FUNCTION_ARGS)1010 intervaltypmodin(PG_FUNCTION_ARGS)
1011 {
1012 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
1013 int32 *tl;
1014 int n;
1015 int32 typmod;
1016
1017 tl = ArrayGetIntegerTypmods(ta, &n);
1018
1019 /*
1020 * tl[0] - interval range (fields bitmask) tl[1] - precision (optional)
1021 *
1022 * Note we must validate tl[0] even though it's normally guaranteed
1023 * correct by the grammar --- consider SELECT 'foo'::"interval"(1000).
1024 */
1025 if (n > 0)
1026 {
1027 switch (tl[0])
1028 {
1029 case INTERVAL_MASK(YEAR):
1030 case INTERVAL_MASK(MONTH):
1031 case INTERVAL_MASK(DAY):
1032 case INTERVAL_MASK(HOUR):
1033 case INTERVAL_MASK(MINUTE):
1034 case INTERVAL_MASK(SECOND):
1035 case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1036 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1037 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1038 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1039 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1040 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1041 case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1042 case INTERVAL_FULL_RANGE:
1043 /* all OK */
1044 break;
1045 default:
1046 ereport(ERROR,
1047 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1048 errmsg("invalid INTERVAL type modifier")));
1049 }
1050 }
1051
1052 if (n == 1)
1053 {
1054 if (tl[0] != INTERVAL_FULL_RANGE)
1055 typmod = INTERVAL_TYPMOD(INTERVAL_FULL_PRECISION, tl[0]);
1056 else
1057 typmod = -1;
1058 }
1059 else if (n == 2)
1060 {
1061 if (tl[1] < 0)
1062 ereport(ERROR,
1063 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1064 errmsg("INTERVAL(%d) precision must not be negative",
1065 tl[1])));
1066 if (tl[1] > MAX_INTERVAL_PRECISION)
1067 {
1068 ereport(WARNING,
1069 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1070 errmsg("INTERVAL(%d) precision reduced to maximum allowed, %d",
1071 tl[1], MAX_INTERVAL_PRECISION)));
1072 typmod = INTERVAL_TYPMOD(MAX_INTERVAL_PRECISION, tl[0]);
1073 }
1074 else
1075 typmod = INTERVAL_TYPMOD(tl[1], tl[0]);
1076 }
1077 else
1078 {
1079 ereport(ERROR,
1080 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1081 errmsg("invalid INTERVAL type modifier")));
1082 typmod = 0; /* keep compiler quiet */
1083 }
1084
1085 PG_RETURN_INT32(typmod);
1086 }
1087
1088 Datum
intervaltypmodout(PG_FUNCTION_ARGS)1089 intervaltypmodout(PG_FUNCTION_ARGS)
1090 {
1091 int32 typmod = PG_GETARG_INT32(0);
1092 char *res = (char *) palloc(64);
1093 int fields;
1094 int precision;
1095 const char *fieldstr;
1096
1097 if (typmod < 0)
1098 {
1099 *res = '\0';
1100 PG_RETURN_CSTRING(res);
1101 }
1102
1103 fields = INTERVAL_RANGE(typmod);
1104 precision = INTERVAL_PRECISION(typmod);
1105
1106 switch (fields)
1107 {
1108 case INTERVAL_MASK(YEAR):
1109 fieldstr = " year";
1110 break;
1111 case INTERVAL_MASK(MONTH):
1112 fieldstr = " month";
1113 break;
1114 case INTERVAL_MASK(DAY):
1115 fieldstr = " day";
1116 break;
1117 case INTERVAL_MASK(HOUR):
1118 fieldstr = " hour";
1119 break;
1120 case INTERVAL_MASK(MINUTE):
1121 fieldstr = " minute";
1122 break;
1123 case INTERVAL_MASK(SECOND):
1124 fieldstr = " second";
1125 break;
1126 case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1127 fieldstr = " year to month";
1128 break;
1129 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1130 fieldstr = " day to hour";
1131 break;
1132 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1133 fieldstr = " day to minute";
1134 break;
1135 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1136 fieldstr = " day to second";
1137 break;
1138 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1139 fieldstr = " hour to minute";
1140 break;
1141 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1142 fieldstr = " hour to second";
1143 break;
1144 case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1145 fieldstr = " minute to second";
1146 break;
1147 case INTERVAL_FULL_RANGE:
1148 fieldstr = "";
1149 break;
1150 default:
1151 elog(ERROR, "invalid INTERVAL typmod: 0x%x", typmod);
1152 fieldstr = "";
1153 break;
1154 }
1155
1156 if (precision != INTERVAL_FULL_PRECISION)
1157 snprintf(res, 64, "%s(%d)", fieldstr, precision);
1158 else
1159 snprintf(res, 64, "%s", fieldstr);
1160
1161 PG_RETURN_CSTRING(res);
1162 }
1163
1164 /*
1165 * Given an interval typmod value, return a code for the least-significant
1166 * field that the typmod allows to be nonzero, for instance given
1167 * INTERVAL DAY TO HOUR we want to identify "hour".
1168 *
1169 * The results should be ordered by field significance, which means
1170 * we can't use the dt.h macros YEAR etc, because for some odd reason
1171 * they aren't ordered that way. Instead, arbitrarily represent
1172 * SECOND = 0, MINUTE = 1, HOUR = 2, DAY = 3, MONTH = 4, YEAR = 5.
1173 */
1174 static int
intervaltypmodleastfield(int32 typmod)1175 intervaltypmodleastfield(int32 typmod)
1176 {
1177 if (typmod < 0)
1178 return 0; /* SECOND */
1179
1180 switch (INTERVAL_RANGE(typmod))
1181 {
1182 case INTERVAL_MASK(YEAR):
1183 return 5; /* YEAR */
1184 case INTERVAL_MASK(MONTH):
1185 return 4; /* MONTH */
1186 case INTERVAL_MASK(DAY):
1187 return 3; /* DAY */
1188 case INTERVAL_MASK(HOUR):
1189 return 2; /* HOUR */
1190 case INTERVAL_MASK(MINUTE):
1191 return 1; /* MINUTE */
1192 case INTERVAL_MASK(SECOND):
1193 return 0; /* SECOND */
1194 case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1195 return 4; /* MONTH */
1196 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1197 return 2; /* HOUR */
1198 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1199 return 1; /* MINUTE */
1200 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1201 return 0; /* SECOND */
1202 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1203 return 1; /* MINUTE */
1204 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1205 return 0; /* SECOND */
1206 case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1207 return 0; /* SECOND */
1208 case INTERVAL_FULL_RANGE:
1209 return 0; /* SECOND */
1210 default:
1211 elog(ERROR, "invalid INTERVAL typmod: 0x%x", typmod);
1212 break;
1213 }
1214 return 0; /* can't get here, but keep compiler quiet */
1215 }
1216
1217
1218 /*
1219 * interval_support()
1220 *
1221 * Planner support function for interval_scale().
1222 *
1223 * Flatten superfluous calls to interval_scale(). The interval typmod is
1224 * complex to permit accepting and regurgitating all SQL standard variations.
1225 * For truncation purposes, it boils down to a single, simple granularity.
1226 */
1227 Datum
interval_support(PG_FUNCTION_ARGS)1228 interval_support(PG_FUNCTION_ARGS)
1229 {
1230 Node *rawreq = (Node *) PG_GETARG_POINTER(0);
1231 Node *ret = NULL;
1232
1233 if (IsA(rawreq, SupportRequestSimplify))
1234 {
1235 SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
1236 FuncExpr *expr = req->fcall;
1237 Node *typmod;
1238
1239 Assert(list_length(expr->args) >= 2);
1240
1241 typmod = (Node *) lsecond(expr->args);
1242
1243 if (IsA(typmod, Const) &&!((Const *) typmod)->constisnull)
1244 {
1245 Node *source = (Node *) linitial(expr->args);
1246 int32 new_typmod = DatumGetInt32(((Const *) typmod)->constvalue);
1247 bool noop;
1248
1249 if (new_typmod < 0)
1250 noop = true;
1251 else
1252 {
1253 int32 old_typmod = exprTypmod(source);
1254 int old_least_field;
1255 int new_least_field;
1256 int old_precis;
1257 int new_precis;
1258
1259 old_least_field = intervaltypmodleastfield(old_typmod);
1260 new_least_field = intervaltypmodleastfield(new_typmod);
1261 if (old_typmod < 0)
1262 old_precis = INTERVAL_FULL_PRECISION;
1263 else
1264 old_precis = INTERVAL_PRECISION(old_typmod);
1265 new_precis = INTERVAL_PRECISION(new_typmod);
1266
1267 /*
1268 * Cast is a no-op if least field stays the same or decreases
1269 * while precision stays the same or increases. But
1270 * precision, which is to say, sub-second precision, only
1271 * affects ranges that include SECOND.
1272 */
1273 noop = (new_least_field <= old_least_field) &&
1274 (old_least_field > 0 /* SECOND */ ||
1275 new_precis >= MAX_INTERVAL_PRECISION ||
1276 new_precis >= old_precis);
1277 }
1278 if (noop)
1279 ret = relabel_to_typmod(source, new_typmod);
1280 }
1281 }
1282
1283 PG_RETURN_POINTER(ret);
1284 }
1285
1286 /* interval_scale()
1287 * Adjust interval type for specified fields.
1288 * Used by PostgreSQL type system to stuff columns.
1289 */
1290 Datum
interval_scale(PG_FUNCTION_ARGS)1291 interval_scale(PG_FUNCTION_ARGS)
1292 {
1293 Interval *interval = PG_GETARG_INTERVAL_P(0);
1294 int32 typmod = PG_GETARG_INT32(1);
1295 Interval *result;
1296
1297 result = palloc(sizeof(Interval));
1298 *result = *interval;
1299
1300 AdjustIntervalForTypmod(result, typmod);
1301
1302 PG_RETURN_INTERVAL_P(result);
1303 }
1304
1305 /*
1306 * Adjust interval for specified precision, in both YEAR to SECOND
1307 * range and sub-second precision.
1308 */
1309 static void
AdjustIntervalForTypmod(Interval * interval,int32 typmod)1310 AdjustIntervalForTypmod(Interval *interval, int32 typmod)
1311 {
1312 static const int64 IntervalScales[MAX_INTERVAL_PRECISION + 1] = {
1313 INT64CONST(1000000),
1314 INT64CONST(100000),
1315 INT64CONST(10000),
1316 INT64CONST(1000),
1317 INT64CONST(100),
1318 INT64CONST(10),
1319 INT64CONST(1)
1320 };
1321
1322 static const int64 IntervalOffsets[MAX_INTERVAL_PRECISION + 1] = {
1323 INT64CONST(500000),
1324 INT64CONST(50000),
1325 INT64CONST(5000),
1326 INT64CONST(500),
1327 INT64CONST(50),
1328 INT64CONST(5),
1329 INT64CONST(0)
1330 };
1331
1332 /*
1333 * Unspecified range and precision? Then not necessary to adjust. Setting
1334 * typmod to -1 is the convention for all data types.
1335 */
1336 if (typmod >= 0)
1337 {
1338 int range = INTERVAL_RANGE(typmod);
1339 int precision = INTERVAL_PRECISION(typmod);
1340
1341 /*
1342 * Our interpretation of intervals with a limited set of fields is
1343 * that fields to the right of the last one specified are zeroed out,
1344 * but those to the left of it remain valid. Thus for example there
1345 * is no operational difference between INTERVAL YEAR TO MONTH and
1346 * INTERVAL MONTH. In some cases we could meaningfully enforce that
1347 * higher-order fields are zero; for example INTERVAL DAY could reject
1348 * nonzero "month" field. However that seems a bit pointless when we
1349 * can't do it consistently. (We cannot enforce a range limit on the
1350 * highest expected field, since we do not have any equivalent of
1351 * SQL's <interval leading field precision>.) If we ever decide to
1352 * revisit this, interval_support will likely require adjusting.
1353 *
1354 * Note: before PG 8.4 we interpreted a limited set of fields as
1355 * actually causing a "modulo" operation on a given value, potentially
1356 * losing high-order as well as low-order information. But there is
1357 * no support for such behavior in the standard, and it seems fairly
1358 * undesirable on data consistency grounds anyway. Now we only
1359 * perform truncation or rounding of low-order fields.
1360 */
1361 if (range == INTERVAL_FULL_RANGE)
1362 {
1363 /* Do nothing... */
1364 }
1365 else if (range == INTERVAL_MASK(YEAR))
1366 {
1367 interval->month = (interval->month / MONTHS_PER_YEAR) * MONTHS_PER_YEAR;
1368 interval->day = 0;
1369 interval->time = 0;
1370 }
1371 else if (range == INTERVAL_MASK(MONTH))
1372 {
1373 interval->day = 0;
1374 interval->time = 0;
1375 }
1376 /* YEAR TO MONTH */
1377 else if (range == (INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH)))
1378 {
1379 interval->day = 0;
1380 interval->time = 0;
1381 }
1382 else if (range == INTERVAL_MASK(DAY))
1383 {
1384 interval->time = 0;
1385 }
1386 else if (range == INTERVAL_MASK(HOUR))
1387 {
1388 interval->time = (interval->time / USECS_PER_HOUR) *
1389 USECS_PER_HOUR;
1390 }
1391 else if (range == INTERVAL_MASK(MINUTE))
1392 {
1393 interval->time = (interval->time / USECS_PER_MINUTE) *
1394 USECS_PER_MINUTE;
1395 }
1396 else if (range == INTERVAL_MASK(SECOND))
1397 {
1398 /* fractional-second rounding will be dealt with below */
1399 }
1400 /* DAY TO HOUR */
1401 else if (range == (INTERVAL_MASK(DAY) |
1402 INTERVAL_MASK(HOUR)))
1403 {
1404 interval->time = (interval->time / USECS_PER_HOUR) *
1405 USECS_PER_HOUR;
1406 }
1407 /* DAY TO MINUTE */
1408 else if (range == (INTERVAL_MASK(DAY) |
1409 INTERVAL_MASK(HOUR) |
1410 INTERVAL_MASK(MINUTE)))
1411 {
1412 interval->time = (interval->time / USECS_PER_MINUTE) *
1413 USECS_PER_MINUTE;
1414 }
1415 /* DAY TO SECOND */
1416 else if (range == (INTERVAL_MASK(DAY) |
1417 INTERVAL_MASK(HOUR) |
1418 INTERVAL_MASK(MINUTE) |
1419 INTERVAL_MASK(SECOND)))
1420 {
1421 /* fractional-second rounding will be dealt with below */
1422 }
1423 /* HOUR TO MINUTE */
1424 else if (range == (INTERVAL_MASK(HOUR) |
1425 INTERVAL_MASK(MINUTE)))
1426 {
1427 interval->time = (interval->time / USECS_PER_MINUTE) *
1428 USECS_PER_MINUTE;
1429 }
1430 /* HOUR TO SECOND */
1431 else if (range == (INTERVAL_MASK(HOUR) |
1432 INTERVAL_MASK(MINUTE) |
1433 INTERVAL_MASK(SECOND)))
1434 {
1435 /* fractional-second rounding will be dealt with below */
1436 }
1437 /* MINUTE TO SECOND */
1438 else if (range == (INTERVAL_MASK(MINUTE) |
1439 INTERVAL_MASK(SECOND)))
1440 {
1441 /* fractional-second rounding will be dealt with below */
1442 }
1443 else
1444 elog(ERROR, "unrecognized interval typmod: %d", typmod);
1445
1446 /* Need to adjust sub-second precision? */
1447 if (precision != INTERVAL_FULL_PRECISION)
1448 {
1449 if (precision < 0 || precision > MAX_INTERVAL_PRECISION)
1450 ereport(ERROR,
1451 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1452 errmsg("interval(%d) precision must be between %d and %d",
1453 precision, 0, MAX_INTERVAL_PRECISION)));
1454
1455 if (interval->time >= INT64CONST(0))
1456 {
1457 interval->time = ((interval->time +
1458 IntervalOffsets[precision]) /
1459 IntervalScales[precision]) *
1460 IntervalScales[precision];
1461 }
1462 else
1463 {
1464 interval->time = -(((-interval->time +
1465 IntervalOffsets[precision]) /
1466 IntervalScales[precision]) *
1467 IntervalScales[precision]);
1468 }
1469 }
1470 }
1471 }
1472
1473 /*
1474 * make_interval - numeric Interval constructor
1475 */
1476 Datum
make_interval(PG_FUNCTION_ARGS)1477 make_interval(PG_FUNCTION_ARGS)
1478 {
1479 int32 years = PG_GETARG_INT32(0);
1480 int32 months = PG_GETARG_INT32(1);
1481 int32 weeks = PG_GETARG_INT32(2);
1482 int32 days = PG_GETARG_INT32(3);
1483 int32 hours = PG_GETARG_INT32(4);
1484 int32 mins = PG_GETARG_INT32(5);
1485 double secs = PG_GETARG_FLOAT8(6);
1486 Interval *result;
1487
1488 /*
1489 * Reject out-of-range inputs. We really ought to check the integer
1490 * inputs as well, but it's not entirely clear what limits to apply.
1491 */
1492 if (isinf(secs) || isnan(secs))
1493 ereport(ERROR,
1494 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1495 errmsg("interval out of range")));
1496
1497 result = (Interval *) palloc(sizeof(Interval));
1498 result->month = years * MONTHS_PER_YEAR + months;
1499 result->day = weeks * 7 + days;
1500
1501 secs = rint(secs * USECS_PER_SEC);
1502 result->time = hours * ((int64) SECS_PER_HOUR * USECS_PER_SEC) +
1503 mins * ((int64) SECS_PER_MINUTE * USECS_PER_SEC) +
1504 (int64) secs;
1505
1506 PG_RETURN_INTERVAL_P(result);
1507 }
1508
1509 /* EncodeSpecialTimestamp()
1510 * Convert reserved timestamp data type to string.
1511 */
1512 void
EncodeSpecialTimestamp(Timestamp dt,char * str)1513 EncodeSpecialTimestamp(Timestamp dt, char *str)
1514 {
1515 if (TIMESTAMP_IS_NOBEGIN(dt))
1516 strcpy(str, EARLY);
1517 else if (TIMESTAMP_IS_NOEND(dt))
1518 strcpy(str, LATE);
1519 else /* shouldn't happen */
1520 elog(ERROR, "invalid argument for EncodeSpecialTimestamp");
1521 }
1522
1523 Datum
now(PG_FUNCTION_ARGS)1524 now(PG_FUNCTION_ARGS)
1525 {
1526 PG_RETURN_TIMESTAMPTZ(GetCurrentTransactionStartTimestamp());
1527 }
1528
1529 Datum
statement_timestamp(PG_FUNCTION_ARGS)1530 statement_timestamp(PG_FUNCTION_ARGS)
1531 {
1532 PG_RETURN_TIMESTAMPTZ(GetCurrentStatementStartTimestamp());
1533 }
1534
1535 Datum
clock_timestamp(PG_FUNCTION_ARGS)1536 clock_timestamp(PG_FUNCTION_ARGS)
1537 {
1538 PG_RETURN_TIMESTAMPTZ(GetCurrentTimestamp());
1539 }
1540
1541 Datum
pg_postmaster_start_time(PG_FUNCTION_ARGS)1542 pg_postmaster_start_time(PG_FUNCTION_ARGS)
1543 {
1544 PG_RETURN_TIMESTAMPTZ(PgStartTime);
1545 }
1546
1547 Datum
pg_conf_load_time(PG_FUNCTION_ARGS)1548 pg_conf_load_time(PG_FUNCTION_ARGS)
1549 {
1550 PG_RETURN_TIMESTAMPTZ(PgReloadTime);
1551 }
1552
1553 /*
1554 * GetCurrentTimestamp -- get the current operating system time
1555 *
1556 * Result is in the form of a TimestampTz value, and is expressed to the
1557 * full precision of the gettimeofday() syscall
1558 */
1559 TimestampTz
GetCurrentTimestamp(void)1560 GetCurrentTimestamp(void)
1561 {
1562 TimestampTz result;
1563 struct timeval tp;
1564
1565 gettimeofday(&tp, NULL);
1566
1567 result = (TimestampTz) tp.tv_sec -
1568 ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
1569 result = (result * USECS_PER_SEC) + tp.tv_usec;
1570
1571 return result;
1572 }
1573
1574 /*
1575 * GetSQLCurrentTimestamp -- implements CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(n)
1576 */
1577 TimestampTz
GetSQLCurrentTimestamp(int32 typmod)1578 GetSQLCurrentTimestamp(int32 typmod)
1579 {
1580 TimestampTz ts;
1581
1582 ts = GetCurrentTransactionStartTimestamp();
1583 if (typmod >= 0)
1584 AdjustTimestampForTypmod(&ts, typmod);
1585 return ts;
1586 }
1587
1588 /*
1589 * GetSQLLocalTimestamp -- implements LOCALTIMESTAMP, LOCALTIMESTAMP(n)
1590 */
1591 Timestamp
GetSQLLocalTimestamp(int32 typmod)1592 GetSQLLocalTimestamp(int32 typmod)
1593 {
1594 Timestamp ts;
1595
1596 ts = timestamptz2timestamp(GetCurrentTransactionStartTimestamp());
1597 if (typmod >= 0)
1598 AdjustTimestampForTypmod(&ts, typmod);
1599 return ts;
1600 }
1601
1602 /*
1603 * timeofday(*) -- returns the current time as a text.
1604 */
1605 Datum
timeofday(PG_FUNCTION_ARGS)1606 timeofday(PG_FUNCTION_ARGS)
1607 {
1608 struct timeval tp;
1609 char templ[128];
1610 char buf[128];
1611 pg_time_t tt;
1612
1613 gettimeofday(&tp, NULL);
1614 tt = (pg_time_t) tp.tv_sec;
1615 pg_strftime(templ, sizeof(templ), "%a %b %d %H:%M:%S.%%06d %Y %Z",
1616 pg_localtime(&tt, session_timezone));
1617 snprintf(buf, sizeof(buf), templ, tp.tv_usec);
1618
1619 PG_RETURN_TEXT_P(cstring_to_text(buf));
1620 }
1621
1622 /*
1623 * TimestampDifference -- convert the difference between two timestamps
1624 * into integer seconds and microseconds
1625 *
1626 * This is typically used to calculate a wait timeout for select(2),
1627 * which explains the otherwise-odd choice of output format.
1628 *
1629 * Both inputs must be ordinary finite timestamps (in current usage,
1630 * they'll be results from GetCurrentTimestamp()).
1631 *
1632 * We expect start_time <= stop_time. If not, we return zeros,
1633 * since then we're already past the previously determined stop_time.
1634 */
1635 void
TimestampDifference(TimestampTz start_time,TimestampTz stop_time,long * secs,int * microsecs)1636 TimestampDifference(TimestampTz start_time, TimestampTz stop_time,
1637 long *secs, int *microsecs)
1638 {
1639 TimestampTz diff = stop_time - start_time;
1640
1641 if (diff <= 0)
1642 {
1643 *secs = 0;
1644 *microsecs = 0;
1645 }
1646 else
1647 {
1648 *secs = (long) (diff / USECS_PER_SEC);
1649 *microsecs = (int) (diff % USECS_PER_SEC);
1650 }
1651 }
1652
1653 /*
1654 * TimestampDifferenceMilliseconds -- convert the difference between two
1655 * timestamps into integer milliseconds
1656 *
1657 * This is typically used to calculate a wait timeout for WaitLatch()
1658 * or a related function. The choice of "long" as the result type
1659 * is to harmonize with that. It is caller's responsibility that the
1660 * input timestamps not be so far apart as to risk overflow of "long"
1661 * (which'd happen at about 25 days on machines with 32-bit "long").
1662 *
1663 * Both inputs must be ordinary finite timestamps (in current usage,
1664 * they'll be results from GetCurrentTimestamp()).
1665 *
1666 * We expect start_time <= stop_time. If not, we return zero,
1667 * since then we're already past the previously determined stop_time.
1668 *
1669 * Note we round up any fractional millisecond, since waiting for just
1670 * less than the intended timeout is undesirable.
1671 */
1672 long
TimestampDifferenceMilliseconds(TimestampTz start_time,TimestampTz stop_time)1673 TimestampDifferenceMilliseconds(TimestampTz start_time, TimestampTz stop_time)
1674 {
1675 TimestampTz diff = stop_time - start_time;
1676
1677 if (diff <= 0)
1678 return 0;
1679 else
1680 return (long) ((diff + 999) / 1000);
1681 }
1682
1683 /*
1684 * TimestampDifferenceExceeds -- report whether the difference between two
1685 * timestamps is >= a threshold (expressed in milliseconds)
1686 *
1687 * Both inputs must be ordinary finite timestamps (in current usage,
1688 * they'll be results from GetCurrentTimestamp()).
1689 */
1690 bool
TimestampDifferenceExceeds(TimestampTz start_time,TimestampTz stop_time,int msec)1691 TimestampDifferenceExceeds(TimestampTz start_time,
1692 TimestampTz stop_time,
1693 int msec)
1694 {
1695 TimestampTz diff = stop_time - start_time;
1696
1697 return (diff >= msec * INT64CONST(1000));
1698 }
1699
1700 /*
1701 * Convert a time_t to TimestampTz.
1702 *
1703 * We do not use time_t internally in Postgres, but this is provided for use
1704 * by functions that need to interpret, say, a stat(2) result.
1705 *
1706 * To avoid having the function's ABI vary depending on the width of time_t,
1707 * we declare the argument as pg_time_t, which is cast-compatible with
1708 * time_t but always 64 bits wide (unless the platform has no 64-bit type).
1709 * This detail should be invisible to callers, at least at source code level.
1710 */
1711 TimestampTz
time_t_to_timestamptz(pg_time_t tm)1712 time_t_to_timestamptz(pg_time_t tm)
1713 {
1714 TimestampTz result;
1715
1716 result = (TimestampTz) tm -
1717 ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
1718 result *= USECS_PER_SEC;
1719
1720 return result;
1721 }
1722
1723 /*
1724 * Convert a TimestampTz to time_t.
1725 *
1726 * This too is just marginally useful, but some places need it.
1727 *
1728 * To avoid having the function's ABI vary depending on the width of time_t,
1729 * we declare the result as pg_time_t, which is cast-compatible with
1730 * time_t but always 64 bits wide (unless the platform has no 64-bit type).
1731 * This detail should be invisible to callers, at least at source code level.
1732 */
1733 pg_time_t
timestamptz_to_time_t(TimestampTz t)1734 timestamptz_to_time_t(TimestampTz t)
1735 {
1736 pg_time_t result;
1737
1738 result = (pg_time_t) (t / USECS_PER_SEC +
1739 ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY));
1740
1741 return result;
1742 }
1743
1744 /*
1745 * Produce a C-string representation of a TimestampTz.
1746 *
1747 * This is mostly for use in emitting messages. The primary difference
1748 * from timestamptz_out is that we force the output format to ISO. Note
1749 * also that the result is in a static buffer, not pstrdup'd.
1750 */
1751 const char *
timestamptz_to_str(TimestampTz t)1752 timestamptz_to_str(TimestampTz t)
1753 {
1754 static char buf[MAXDATELEN + 1];
1755 int tz;
1756 struct pg_tm tt,
1757 *tm = &tt;
1758 fsec_t fsec;
1759 const char *tzn;
1760
1761 if (TIMESTAMP_NOT_FINITE(t))
1762 EncodeSpecialTimestamp(t, buf);
1763 else if (timestamp2tm(t, &tz, tm, &fsec, &tzn, NULL) == 0)
1764 EncodeDateTime(tm, fsec, true, tz, tzn, USE_ISO_DATES, buf);
1765 else
1766 strlcpy(buf, "(timestamp out of range)", sizeof(buf));
1767
1768 return buf;
1769 }
1770
1771
1772 void
dt2time(Timestamp jd,int * hour,int * min,int * sec,fsec_t * fsec)1773 dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec)
1774 {
1775 TimeOffset time;
1776
1777 time = jd;
1778
1779 *hour = time / USECS_PER_HOUR;
1780 time -= (*hour) * USECS_PER_HOUR;
1781 *min = time / USECS_PER_MINUTE;
1782 time -= (*min) * USECS_PER_MINUTE;
1783 *sec = time / USECS_PER_SEC;
1784 *fsec = time - (*sec * USECS_PER_SEC);
1785 } /* dt2time() */
1786
1787
1788 /*
1789 * timestamp2tm() - Convert timestamp data type to POSIX time structure.
1790 *
1791 * Note that year is _not_ 1900-based, but is an explicit full value.
1792 * Also, month is one-based, _not_ zero-based.
1793 * Returns:
1794 * 0 on success
1795 * -1 on out of range
1796 *
1797 * If attimezone is NULL, the global timezone setting will be used.
1798 */
1799 int
timestamp2tm(Timestamp dt,int * tzp,struct pg_tm * tm,fsec_t * fsec,const char ** tzn,pg_tz * attimezone)1800 timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm, fsec_t *fsec, const char **tzn, pg_tz *attimezone)
1801 {
1802 Timestamp date;
1803 Timestamp time;
1804 pg_time_t utime;
1805
1806 /* Use session timezone if caller asks for default */
1807 if (attimezone == NULL)
1808 attimezone = session_timezone;
1809
1810 time = dt;
1811 TMODULO(time, date, USECS_PER_DAY);
1812
1813 if (time < INT64CONST(0))
1814 {
1815 time += USECS_PER_DAY;
1816 date -= 1;
1817 }
1818
1819 /* add offset to go from J2000 back to standard Julian date */
1820 date += POSTGRES_EPOCH_JDATE;
1821
1822 /* Julian day routine does not work for negative Julian days */
1823 if (date < 0 || date > (Timestamp) INT_MAX)
1824 return -1;
1825
1826 j2date((int) date, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
1827 dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
1828
1829 /* Done if no TZ conversion wanted */
1830 if (tzp == NULL)
1831 {
1832 tm->tm_isdst = -1;
1833 tm->tm_gmtoff = 0;
1834 tm->tm_zone = NULL;
1835 if (tzn != NULL)
1836 *tzn = NULL;
1837 return 0;
1838 }
1839
1840 /*
1841 * If the time falls within the range of pg_time_t, use pg_localtime() to
1842 * rotate to the local time zone.
1843 *
1844 * First, convert to an integral timestamp, avoiding possibly
1845 * platform-specific roundoff-in-wrong-direction errors, and adjust to
1846 * Unix epoch. Then see if we can convert to pg_time_t without loss. This
1847 * coding avoids hardwiring any assumptions about the width of pg_time_t,
1848 * so it should behave sanely on machines without int64.
1849 */
1850 dt = (dt - *fsec) / USECS_PER_SEC +
1851 (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY;
1852 utime = (pg_time_t) dt;
1853 if ((Timestamp) utime == dt)
1854 {
1855 struct pg_tm *tx = pg_localtime(&utime, attimezone);
1856
1857 tm->tm_year = tx->tm_year + 1900;
1858 tm->tm_mon = tx->tm_mon + 1;
1859 tm->tm_mday = tx->tm_mday;
1860 tm->tm_hour = tx->tm_hour;
1861 tm->tm_min = tx->tm_min;
1862 tm->tm_sec = tx->tm_sec;
1863 tm->tm_isdst = tx->tm_isdst;
1864 tm->tm_gmtoff = tx->tm_gmtoff;
1865 tm->tm_zone = tx->tm_zone;
1866 *tzp = -tm->tm_gmtoff;
1867 if (tzn != NULL)
1868 *tzn = tm->tm_zone;
1869 }
1870 else
1871 {
1872 /*
1873 * When out of range of pg_time_t, treat as GMT
1874 */
1875 *tzp = 0;
1876 /* Mark this as *no* time zone available */
1877 tm->tm_isdst = -1;
1878 tm->tm_gmtoff = 0;
1879 tm->tm_zone = NULL;
1880 if (tzn != NULL)
1881 *tzn = NULL;
1882 }
1883
1884 return 0;
1885 }
1886
1887
1888 /* tm2timestamp()
1889 * Convert a tm structure to a timestamp data type.
1890 * Note that year is _not_ 1900-based, but is an explicit full value.
1891 * Also, month is one-based, _not_ zero-based.
1892 *
1893 * Returns -1 on failure (value out of range).
1894 */
1895 int
tm2timestamp(struct pg_tm * tm,fsec_t fsec,int * tzp,Timestamp * result)1896 tm2timestamp(struct pg_tm *tm, fsec_t fsec, int *tzp, Timestamp *result)
1897 {
1898 TimeOffset date;
1899 TimeOffset time;
1900
1901 /* Prevent overflow in Julian-day routines */
1902 if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
1903 {
1904 *result = 0; /* keep compiler quiet */
1905 return -1;
1906 }
1907
1908 date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
1909 time = time2t(tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
1910
1911 *result = date * USECS_PER_DAY + time;
1912 /* check for major overflow */
1913 if ((*result - time) / USECS_PER_DAY != date)
1914 {
1915 *result = 0; /* keep compiler quiet */
1916 return -1;
1917 }
1918 /* check for just-barely overflow (okay except time-of-day wraps) */
1919 /* caution: we want to allow 1999-12-31 24:00:00 */
1920 if ((*result < 0 && date > 0) ||
1921 (*result > 0 && date < -1))
1922 {
1923 *result = 0; /* keep compiler quiet */
1924 return -1;
1925 }
1926 if (tzp != NULL)
1927 *result = dt2local(*result, -(*tzp));
1928
1929 /* final range check catches just-out-of-range timestamps */
1930 if (!IS_VALID_TIMESTAMP(*result))
1931 {
1932 *result = 0; /* keep compiler quiet */
1933 return -1;
1934 }
1935
1936 return 0;
1937 }
1938
1939
1940 /* interval2tm()
1941 * Convert an interval data type to a tm structure.
1942 */
1943 int
interval2tm(Interval span,struct pg_tm * tm,fsec_t * fsec)1944 interval2tm(Interval span, struct pg_tm *tm, fsec_t *fsec)
1945 {
1946 TimeOffset time;
1947 TimeOffset tfrac;
1948
1949 tm->tm_year = span.month / MONTHS_PER_YEAR;
1950 tm->tm_mon = span.month % MONTHS_PER_YEAR;
1951 tm->tm_mday = span.day;
1952 time = span.time;
1953
1954 tfrac = time / USECS_PER_HOUR;
1955 time -= tfrac * USECS_PER_HOUR;
1956 tm->tm_hour = tfrac;
1957 if (!SAMESIGN(tm->tm_hour, tfrac))
1958 ereport(ERROR,
1959 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1960 errmsg("interval out of range")));
1961 tfrac = time / USECS_PER_MINUTE;
1962 time -= tfrac * USECS_PER_MINUTE;
1963 tm->tm_min = tfrac;
1964 tfrac = time / USECS_PER_SEC;
1965 *fsec = time - (tfrac * USECS_PER_SEC);
1966 tm->tm_sec = tfrac;
1967
1968 return 0;
1969 }
1970
1971 int
tm2interval(struct pg_tm * tm,fsec_t fsec,Interval * span)1972 tm2interval(struct pg_tm *tm, fsec_t fsec, Interval *span)
1973 {
1974 double total_months = (double) tm->tm_year * MONTHS_PER_YEAR + tm->tm_mon;
1975
1976 if (total_months > INT_MAX || total_months < INT_MIN)
1977 return -1;
1978 span->month = total_months;
1979 span->day = tm->tm_mday;
1980 span->time = (((((tm->tm_hour * INT64CONST(60)) +
1981 tm->tm_min) * INT64CONST(60)) +
1982 tm->tm_sec) * USECS_PER_SEC) + fsec;
1983
1984 return 0;
1985 }
1986
1987 static TimeOffset
time2t(const int hour,const int min,const int sec,const fsec_t fsec)1988 time2t(const int hour, const int min, const int sec, const fsec_t fsec)
1989 {
1990 return (((((hour * MINS_PER_HOUR) + min) * SECS_PER_MINUTE) + sec) * USECS_PER_SEC) + fsec;
1991 }
1992
1993 static Timestamp
dt2local(Timestamp dt,int tz)1994 dt2local(Timestamp dt, int tz)
1995 {
1996 dt -= (tz * USECS_PER_SEC);
1997 return dt;
1998 }
1999
2000
2001 /*****************************************************************************
2002 * PUBLIC ROUTINES *
2003 *****************************************************************************/
2004
2005
2006 Datum
timestamp_finite(PG_FUNCTION_ARGS)2007 timestamp_finite(PG_FUNCTION_ARGS)
2008 {
2009 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2010
2011 PG_RETURN_BOOL(!TIMESTAMP_NOT_FINITE(timestamp));
2012 }
2013
2014 Datum
interval_finite(PG_FUNCTION_ARGS)2015 interval_finite(PG_FUNCTION_ARGS)
2016 {
2017 PG_RETURN_BOOL(true);
2018 }
2019
2020
2021 /*----------------------------------------------------------
2022 * Relational operators for timestamp.
2023 *---------------------------------------------------------*/
2024
2025 void
GetEpochTime(struct pg_tm * tm)2026 GetEpochTime(struct pg_tm *tm)
2027 {
2028 struct pg_tm *t0;
2029 pg_time_t epoch = 0;
2030
2031 t0 = pg_gmtime(&epoch);
2032
2033 if (t0 == NULL)
2034 elog(ERROR, "could not convert epoch to timestamp: %m");
2035
2036 tm->tm_year = t0->tm_year;
2037 tm->tm_mon = t0->tm_mon;
2038 tm->tm_mday = t0->tm_mday;
2039 tm->tm_hour = t0->tm_hour;
2040 tm->tm_min = t0->tm_min;
2041 tm->tm_sec = t0->tm_sec;
2042
2043 tm->tm_year += 1900;
2044 tm->tm_mon++;
2045 }
2046
2047 Timestamp
SetEpochTimestamp(void)2048 SetEpochTimestamp(void)
2049 {
2050 Timestamp dt;
2051 struct pg_tm tt,
2052 *tm = &tt;
2053
2054 GetEpochTime(tm);
2055 /* we don't bother to test for failure ... */
2056 tm2timestamp(tm, 0, NULL, &dt);
2057
2058 return dt;
2059 } /* SetEpochTimestamp() */
2060
2061 /*
2062 * We are currently sharing some code between timestamp and timestamptz.
2063 * The comparison functions are among them. - thomas 2001-09-25
2064 *
2065 * timestamp_relop - is timestamp1 relop timestamp2
2066 */
2067 int
timestamp_cmp_internal(Timestamp dt1,Timestamp dt2)2068 timestamp_cmp_internal(Timestamp dt1, Timestamp dt2)
2069 {
2070 return (dt1 < dt2) ? -1 : ((dt1 > dt2) ? 1 : 0);
2071 }
2072
2073 Datum
timestamp_eq(PG_FUNCTION_ARGS)2074 timestamp_eq(PG_FUNCTION_ARGS)
2075 {
2076 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2077 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2078
2079 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
2080 }
2081
2082 Datum
timestamp_ne(PG_FUNCTION_ARGS)2083 timestamp_ne(PG_FUNCTION_ARGS)
2084 {
2085 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2086 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2087
2088 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
2089 }
2090
2091 Datum
timestamp_lt(PG_FUNCTION_ARGS)2092 timestamp_lt(PG_FUNCTION_ARGS)
2093 {
2094 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2095 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2096
2097 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
2098 }
2099
2100 Datum
timestamp_gt(PG_FUNCTION_ARGS)2101 timestamp_gt(PG_FUNCTION_ARGS)
2102 {
2103 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2104 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2105
2106 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
2107 }
2108
2109 Datum
timestamp_le(PG_FUNCTION_ARGS)2110 timestamp_le(PG_FUNCTION_ARGS)
2111 {
2112 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2113 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2114
2115 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
2116 }
2117
2118 Datum
timestamp_ge(PG_FUNCTION_ARGS)2119 timestamp_ge(PG_FUNCTION_ARGS)
2120 {
2121 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2122 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2123
2124 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
2125 }
2126
2127 Datum
timestamp_cmp(PG_FUNCTION_ARGS)2128 timestamp_cmp(PG_FUNCTION_ARGS)
2129 {
2130 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2131 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2132
2133 PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
2134 }
2135
2136 /* note: this is used for timestamptz also */
2137 static int
timestamp_fastcmp(Datum x,Datum y,SortSupport ssup)2138 timestamp_fastcmp(Datum x, Datum y, SortSupport ssup)
2139 {
2140 Timestamp a = DatumGetTimestamp(x);
2141 Timestamp b = DatumGetTimestamp(y);
2142
2143 return timestamp_cmp_internal(a, b);
2144 }
2145
2146 Datum
timestamp_sortsupport(PG_FUNCTION_ARGS)2147 timestamp_sortsupport(PG_FUNCTION_ARGS)
2148 {
2149 SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0);
2150
2151 ssup->comparator = timestamp_fastcmp;
2152 PG_RETURN_VOID();
2153 }
2154
2155 Datum
timestamp_hash(PG_FUNCTION_ARGS)2156 timestamp_hash(PG_FUNCTION_ARGS)
2157 {
2158 return hashint8(fcinfo);
2159 }
2160
2161 Datum
timestamp_hash_extended(PG_FUNCTION_ARGS)2162 timestamp_hash_extended(PG_FUNCTION_ARGS)
2163 {
2164 return hashint8extended(fcinfo);
2165 }
2166
2167 /*
2168 * Cross-type comparison functions for timestamp vs timestamptz
2169 */
2170
2171 Datum
timestamp_eq_timestamptz(PG_FUNCTION_ARGS)2172 timestamp_eq_timestamptz(PG_FUNCTION_ARGS)
2173 {
2174 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2175 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2176 TimestampTz dt1;
2177
2178 dt1 = timestamp2timestamptz(timestampVal);
2179
2180 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
2181 }
2182
2183 Datum
timestamp_ne_timestamptz(PG_FUNCTION_ARGS)2184 timestamp_ne_timestamptz(PG_FUNCTION_ARGS)
2185 {
2186 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2187 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2188 TimestampTz dt1;
2189
2190 dt1 = timestamp2timestamptz(timestampVal);
2191
2192 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
2193 }
2194
2195 Datum
timestamp_lt_timestamptz(PG_FUNCTION_ARGS)2196 timestamp_lt_timestamptz(PG_FUNCTION_ARGS)
2197 {
2198 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2199 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2200 TimestampTz dt1;
2201
2202 dt1 = timestamp2timestamptz(timestampVal);
2203
2204 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
2205 }
2206
2207 Datum
timestamp_gt_timestamptz(PG_FUNCTION_ARGS)2208 timestamp_gt_timestamptz(PG_FUNCTION_ARGS)
2209 {
2210 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2211 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2212 TimestampTz dt1;
2213
2214 dt1 = timestamp2timestamptz(timestampVal);
2215
2216 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
2217 }
2218
2219 Datum
timestamp_le_timestamptz(PG_FUNCTION_ARGS)2220 timestamp_le_timestamptz(PG_FUNCTION_ARGS)
2221 {
2222 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2223 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2224 TimestampTz dt1;
2225
2226 dt1 = timestamp2timestamptz(timestampVal);
2227
2228 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
2229 }
2230
2231 Datum
timestamp_ge_timestamptz(PG_FUNCTION_ARGS)2232 timestamp_ge_timestamptz(PG_FUNCTION_ARGS)
2233 {
2234 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2235 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2236 TimestampTz dt1;
2237
2238 dt1 = timestamp2timestamptz(timestampVal);
2239
2240 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
2241 }
2242
2243 Datum
timestamp_cmp_timestamptz(PG_FUNCTION_ARGS)2244 timestamp_cmp_timestamptz(PG_FUNCTION_ARGS)
2245 {
2246 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2247 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2248 TimestampTz dt1;
2249
2250 dt1 = timestamp2timestamptz(timestampVal);
2251
2252 PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
2253 }
2254
2255 Datum
timestamptz_eq_timestamp(PG_FUNCTION_ARGS)2256 timestamptz_eq_timestamp(PG_FUNCTION_ARGS)
2257 {
2258 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2259 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2260 TimestampTz dt2;
2261
2262 dt2 = timestamp2timestamptz(timestampVal);
2263
2264 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
2265 }
2266
2267 Datum
timestamptz_ne_timestamp(PG_FUNCTION_ARGS)2268 timestamptz_ne_timestamp(PG_FUNCTION_ARGS)
2269 {
2270 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2271 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2272 TimestampTz dt2;
2273
2274 dt2 = timestamp2timestamptz(timestampVal);
2275
2276 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
2277 }
2278
2279 Datum
timestamptz_lt_timestamp(PG_FUNCTION_ARGS)2280 timestamptz_lt_timestamp(PG_FUNCTION_ARGS)
2281 {
2282 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2283 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2284 TimestampTz dt2;
2285
2286 dt2 = timestamp2timestamptz(timestampVal);
2287
2288 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
2289 }
2290
2291 Datum
timestamptz_gt_timestamp(PG_FUNCTION_ARGS)2292 timestamptz_gt_timestamp(PG_FUNCTION_ARGS)
2293 {
2294 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2295 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2296 TimestampTz dt2;
2297
2298 dt2 = timestamp2timestamptz(timestampVal);
2299
2300 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
2301 }
2302
2303 Datum
timestamptz_le_timestamp(PG_FUNCTION_ARGS)2304 timestamptz_le_timestamp(PG_FUNCTION_ARGS)
2305 {
2306 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2307 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2308 TimestampTz dt2;
2309
2310 dt2 = timestamp2timestamptz(timestampVal);
2311
2312 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
2313 }
2314
2315 Datum
timestamptz_ge_timestamp(PG_FUNCTION_ARGS)2316 timestamptz_ge_timestamp(PG_FUNCTION_ARGS)
2317 {
2318 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2319 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2320 TimestampTz dt2;
2321
2322 dt2 = timestamp2timestamptz(timestampVal);
2323
2324 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
2325 }
2326
2327 Datum
timestamptz_cmp_timestamp(PG_FUNCTION_ARGS)2328 timestamptz_cmp_timestamp(PG_FUNCTION_ARGS)
2329 {
2330 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2331 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2332 TimestampTz dt2;
2333
2334 dt2 = timestamp2timestamptz(timestampVal);
2335
2336 PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
2337 }
2338
2339
2340 /*
2341 * interval_relop - is interval1 relop interval2
2342 *
2343 * Interval comparison is based on converting interval values to a linear
2344 * representation expressed in the units of the time field (microseconds,
2345 * in the case of integer timestamps) with days assumed to be always 24 hours
2346 * and months assumed to be always 30 days. To avoid overflow, we need a
2347 * wider-than-int64 datatype for the linear representation, so use INT128.
2348 */
2349
2350 static inline INT128
interval_cmp_value(const Interval * interval)2351 interval_cmp_value(const Interval *interval)
2352 {
2353 INT128 span;
2354 int64 dayfraction;
2355 int64 days;
2356
2357 /*
2358 * Separate time field into days and dayfraction, then add the month and
2359 * day fields to the days part. We cannot overflow int64 days here.
2360 */
2361 dayfraction = interval->time % USECS_PER_DAY;
2362 days = interval->time / USECS_PER_DAY;
2363 days += interval->month * INT64CONST(30);
2364 days += interval->day;
2365
2366 /* Widen dayfraction to 128 bits */
2367 span = int64_to_int128(dayfraction);
2368
2369 /* Scale up days to microseconds, forming a 128-bit product */
2370 int128_add_int64_mul_int64(&span, days, USECS_PER_DAY);
2371
2372 return span;
2373 }
2374
2375 static int
interval_cmp_internal(Interval * interval1,Interval * interval2)2376 interval_cmp_internal(Interval *interval1, Interval *interval2)
2377 {
2378 INT128 span1 = interval_cmp_value(interval1);
2379 INT128 span2 = interval_cmp_value(interval2);
2380
2381 return int128_compare(span1, span2);
2382 }
2383
2384 Datum
interval_eq(PG_FUNCTION_ARGS)2385 interval_eq(PG_FUNCTION_ARGS)
2386 {
2387 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2388 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2389
2390 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) == 0);
2391 }
2392
2393 Datum
interval_ne(PG_FUNCTION_ARGS)2394 interval_ne(PG_FUNCTION_ARGS)
2395 {
2396 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2397 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2398
2399 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) != 0);
2400 }
2401
2402 Datum
interval_lt(PG_FUNCTION_ARGS)2403 interval_lt(PG_FUNCTION_ARGS)
2404 {
2405 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2406 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2407
2408 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) < 0);
2409 }
2410
2411 Datum
interval_gt(PG_FUNCTION_ARGS)2412 interval_gt(PG_FUNCTION_ARGS)
2413 {
2414 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2415 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2416
2417 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) > 0);
2418 }
2419
2420 Datum
interval_le(PG_FUNCTION_ARGS)2421 interval_le(PG_FUNCTION_ARGS)
2422 {
2423 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2424 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2425
2426 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) <= 0);
2427 }
2428
2429 Datum
interval_ge(PG_FUNCTION_ARGS)2430 interval_ge(PG_FUNCTION_ARGS)
2431 {
2432 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2433 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2434
2435 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) >= 0);
2436 }
2437
2438 Datum
interval_cmp(PG_FUNCTION_ARGS)2439 interval_cmp(PG_FUNCTION_ARGS)
2440 {
2441 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2442 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2443
2444 PG_RETURN_INT32(interval_cmp_internal(interval1, interval2));
2445 }
2446
2447 /*
2448 * Hashing for intervals
2449 *
2450 * We must produce equal hashvals for values that interval_cmp_internal()
2451 * considers equal. So, compute the net span the same way it does,
2452 * and then hash that.
2453 */
2454 Datum
interval_hash(PG_FUNCTION_ARGS)2455 interval_hash(PG_FUNCTION_ARGS)
2456 {
2457 Interval *interval = PG_GETARG_INTERVAL_P(0);
2458 INT128 span = interval_cmp_value(interval);
2459 int64 span64;
2460
2461 /*
2462 * Use only the least significant 64 bits for hashing. The upper 64 bits
2463 * seldom add any useful information, and besides we must do it like this
2464 * for compatibility with hashes calculated before use of INT128 was
2465 * introduced.
2466 */
2467 span64 = int128_to_int64(span);
2468
2469 return DirectFunctionCall1(hashint8, Int64GetDatumFast(span64));
2470 }
2471
2472 Datum
interval_hash_extended(PG_FUNCTION_ARGS)2473 interval_hash_extended(PG_FUNCTION_ARGS)
2474 {
2475 Interval *interval = PG_GETARG_INTERVAL_P(0);
2476 INT128 span = interval_cmp_value(interval);
2477 int64 span64;
2478
2479 /* Same approach as interval_hash */
2480 span64 = int128_to_int64(span);
2481
2482 return DirectFunctionCall2(hashint8extended, Int64GetDatumFast(span64),
2483 PG_GETARG_DATUM(1));
2484 }
2485
2486 /* overlaps_timestamp() --- implements the SQL OVERLAPS operator.
2487 *
2488 * Algorithm is per SQL spec. This is much harder than you'd think
2489 * because the spec requires us to deliver a non-null answer in some cases
2490 * where some of the inputs are null.
2491 */
2492 Datum
overlaps_timestamp(PG_FUNCTION_ARGS)2493 overlaps_timestamp(PG_FUNCTION_ARGS)
2494 {
2495 /*
2496 * The arguments are Timestamps, but we leave them as generic Datums to
2497 * avoid unnecessary conversions between value and reference forms --- not
2498 * to mention possible dereferences of null pointers.
2499 */
2500 Datum ts1 = PG_GETARG_DATUM(0);
2501 Datum te1 = PG_GETARG_DATUM(1);
2502 Datum ts2 = PG_GETARG_DATUM(2);
2503 Datum te2 = PG_GETARG_DATUM(3);
2504 bool ts1IsNull = PG_ARGISNULL(0);
2505 bool te1IsNull = PG_ARGISNULL(1);
2506 bool ts2IsNull = PG_ARGISNULL(2);
2507 bool te2IsNull = PG_ARGISNULL(3);
2508
2509 #define TIMESTAMP_GT(t1,t2) \
2510 DatumGetBool(DirectFunctionCall2(timestamp_gt,t1,t2))
2511 #define TIMESTAMP_LT(t1,t2) \
2512 DatumGetBool(DirectFunctionCall2(timestamp_lt,t1,t2))
2513
2514 /*
2515 * If both endpoints of interval 1 are null, the result is null (unknown).
2516 * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
2517 * take ts1 as the lesser endpoint.
2518 */
2519 if (ts1IsNull)
2520 {
2521 if (te1IsNull)
2522 PG_RETURN_NULL();
2523 /* swap null for non-null */
2524 ts1 = te1;
2525 te1IsNull = true;
2526 }
2527 else if (!te1IsNull)
2528 {
2529 if (TIMESTAMP_GT(ts1, te1))
2530 {
2531 Datum tt = ts1;
2532
2533 ts1 = te1;
2534 te1 = tt;
2535 }
2536 }
2537
2538 /* Likewise for interval 2. */
2539 if (ts2IsNull)
2540 {
2541 if (te2IsNull)
2542 PG_RETURN_NULL();
2543 /* swap null for non-null */
2544 ts2 = te2;
2545 te2IsNull = true;
2546 }
2547 else if (!te2IsNull)
2548 {
2549 if (TIMESTAMP_GT(ts2, te2))
2550 {
2551 Datum tt = ts2;
2552
2553 ts2 = te2;
2554 te2 = tt;
2555 }
2556 }
2557
2558 /*
2559 * At this point neither ts1 nor ts2 is null, so we can consider three
2560 * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
2561 */
2562 if (TIMESTAMP_GT(ts1, ts2))
2563 {
2564 /*
2565 * This case is ts1 < te2 OR te1 < te2, which may look redundant but
2566 * in the presence of nulls it's not quite completely so.
2567 */
2568 if (te2IsNull)
2569 PG_RETURN_NULL();
2570 if (TIMESTAMP_LT(ts1, te2))
2571 PG_RETURN_BOOL(true);
2572 if (te1IsNull)
2573 PG_RETURN_NULL();
2574
2575 /*
2576 * If te1 is not null then we had ts1 <= te1 above, and we just found
2577 * ts1 >= te2, hence te1 >= te2.
2578 */
2579 PG_RETURN_BOOL(false);
2580 }
2581 else if (TIMESTAMP_LT(ts1, ts2))
2582 {
2583 /* This case is ts2 < te1 OR te2 < te1 */
2584 if (te1IsNull)
2585 PG_RETURN_NULL();
2586 if (TIMESTAMP_LT(ts2, te1))
2587 PG_RETURN_BOOL(true);
2588 if (te2IsNull)
2589 PG_RETURN_NULL();
2590
2591 /*
2592 * If te2 is not null then we had ts2 <= te2 above, and we just found
2593 * ts2 >= te1, hence te2 >= te1.
2594 */
2595 PG_RETURN_BOOL(false);
2596 }
2597 else
2598 {
2599 /*
2600 * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
2601 * rather silly way of saying "true if both are non-null, else null".
2602 */
2603 if (te1IsNull || te2IsNull)
2604 PG_RETURN_NULL();
2605 PG_RETURN_BOOL(true);
2606 }
2607
2608 #undef TIMESTAMP_GT
2609 #undef TIMESTAMP_LT
2610 }
2611
2612
2613 /*----------------------------------------------------------
2614 * "Arithmetic" operators on date/times.
2615 *---------------------------------------------------------*/
2616
2617 Datum
timestamp_smaller(PG_FUNCTION_ARGS)2618 timestamp_smaller(PG_FUNCTION_ARGS)
2619 {
2620 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2621 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2622 Timestamp result;
2623
2624 /* use timestamp_cmp_internal to be sure this agrees with comparisons */
2625 if (timestamp_cmp_internal(dt1, dt2) < 0)
2626 result = dt1;
2627 else
2628 result = dt2;
2629 PG_RETURN_TIMESTAMP(result);
2630 }
2631
2632 Datum
timestamp_larger(PG_FUNCTION_ARGS)2633 timestamp_larger(PG_FUNCTION_ARGS)
2634 {
2635 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2636 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2637 Timestamp result;
2638
2639 if (timestamp_cmp_internal(dt1, dt2) > 0)
2640 result = dt1;
2641 else
2642 result = dt2;
2643 PG_RETURN_TIMESTAMP(result);
2644 }
2645
2646
2647 Datum
timestamp_mi(PG_FUNCTION_ARGS)2648 timestamp_mi(PG_FUNCTION_ARGS)
2649 {
2650 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2651 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2652 Interval *result;
2653
2654 result = (Interval *) palloc(sizeof(Interval));
2655
2656 if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
2657 ereport(ERROR,
2658 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2659 errmsg("cannot subtract infinite timestamps")));
2660
2661 result->time = dt1 - dt2;
2662
2663 result->month = 0;
2664 result->day = 0;
2665
2666 /*----------
2667 * This is wrong, but removing it breaks a lot of regression tests.
2668 * For example:
2669 *
2670 * test=> SET timezone = 'EST5EDT';
2671 * test=> SELECT
2672 * test-> ('2005-10-30 13:22:00-05'::timestamptz -
2673 * test(> '2005-10-29 13:22:00-04'::timestamptz);
2674 * ?column?
2675 * ----------------
2676 * 1 day 01:00:00
2677 * (1 row)
2678 *
2679 * so adding that to the first timestamp gets:
2680 *
2681 * test=> SELECT
2682 * test-> ('2005-10-29 13:22:00-04'::timestamptz +
2683 * test(> ('2005-10-30 13:22:00-05'::timestamptz -
2684 * test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
2685 * timezone
2686 * --------------------
2687 * 2005-10-30 14:22:00
2688 * (1 row)
2689 *----------
2690 */
2691 result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours,
2692 IntervalPGetDatum(result)));
2693
2694 PG_RETURN_INTERVAL_P(result);
2695 }
2696
2697 /*
2698 * interval_justify_interval()
2699 *
2700 * Adjust interval so 'month', 'day', and 'time' portions are within
2701 * customary bounds. Specifically:
2702 *
2703 * 0 <= abs(time) < 24 hours
2704 * 0 <= abs(day) < 30 days
2705 *
2706 * Also, the sign bit on all three fields is made equal, so either
2707 * all three fields are negative or all are positive.
2708 */
2709 Datum
interval_justify_interval(PG_FUNCTION_ARGS)2710 interval_justify_interval(PG_FUNCTION_ARGS)
2711 {
2712 Interval *span = PG_GETARG_INTERVAL_P(0);
2713 Interval *result;
2714 TimeOffset wholeday;
2715 int32 wholemonth;
2716
2717 result = (Interval *) palloc(sizeof(Interval));
2718 result->month = span->month;
2719 result->day = span->day;
2720 result->time = span->time;
2721
2722 TMODULO(result->time, wholeday, USECS_PER_DAY);
2723 result->day += wholeday; /* could overflow... */
2724
2725 wholemonth = result->day / DAYS_PER_MONTH;
2726 result->day -= wholemonth * DAYS_PER_MONTH;
2727 result->month += wholemonth;
2728
2729 if (result->month > 0 &&
2730 (result->day < 0 || (result->day == 0 && result->time < 0)))
2731 {
2732 result->day += DAYS_PER_MONTH;
2733 result->month--;
2734 }
2735 else if (result->month < 0 &&
2736 (result->day > 0 || (result->day == 0 && result->time > 0)))
2737 {
2738 result->day -= DAYS_PER_MONTH;
2739 result->month++;
2740 }
2741
2742 if (result->day > 0 && result->time < 0)
2743 {
2744 result->time += USECS_PER_DAY;
2745 result->day--;
2746 }
2747 else if (result->day < 0 && result->time > 0)
2748 {
2749 result->time -= USECS_PER_DAY;
2750 result->day++;
2751 }
2752
2753 PG_RETURN_INTERVAL_P(result);
2754 }
2755
2756 /*
2757 * interval_justify_hours()
2758 *
2759 * Adjust interval so 'time' contains less than a whole day, adding
2760 * the excess to 'day'. This is useful for
2761 * situations (such as non-TZ) where '1 day' = '24 hours' is valid,
2762 * e.g. interval subtraction and division.
2763 */
2764 Datum
interval_justify_hours(PG_FUNCTION_ARGS)2765 interval_justify_hours(PG_FUNCTION_ARGS)
2766 {
2767 Interval *span = PG_GETARG_INTERVAL_P(0);
2768 Interval *result;
2769 TimeOffset wholeday;
2770
2771 result = (Interval *) palloc(sizeof(Interval));
2772 result->month = span->month;
2773 result->day = span->day;
2774 result->time = span->time;
2775
2776 TMODULO(result->time, wholeday, USECS_PER_DAY);
2777 result->day += wholeday; /* could overflow... */
2778
2779 if (result->day > 0 && result->time < 0)
2780 {
2781 result->time += USECS_PER_DAY;
2782 result->day--;
2783 }
2784 else if (result->day < 0 && result->time > 0)
2785 {
2786 result->time -= USECS_PER_DAY;
2787 result->day++;
2788 }
2789
2790 PG_RETURN_INTERVAL_P(result);
2791 }
2792
2793 /*
2794 * interval_justify_days()
2795 *
2796 * Adjust interval so 'day' contains less than 30 days, adding
2797 * the excess to 'month'.
2798 */
2799 Datum
interval_justify_days(PG_FUNCTION_ARGS)2800 interval_justify_days(PG_FUNCTION_ARGS)
2801 {
2802 Interval *span = PG_GETARG_INTERVAL_P(0);
2803 Interval *result;
2804 int32 wholemonth;
2805
2806 result = (Interval *) palloc(sizeof(Interval));
2807 result->month = span->month;
2808 result->day = span->day;
2809 result->time = span->time;
2810
2811 wholemonth = result->day / DAYS_PER_MONTH;
2812 result->day -= wholemonth * DAYS_PER_MONTH;
2813 result->month += wholemonth;
2814
2815 if (result->month > 0 && result->day < 0)
2816 {
2817 result->day += DAYS_PER_MONTH;
2818 result->month--;
2819 }
2820 else if (result->month < 0 && result->day > 0)
2821 {
2822 result->day -= DAYS_PER_MONTH;
2823 result->month++;
2824 }
2825
2826 PG_RETURN_INTERVAL_P(result);
2827 }
2828
2829 /* timestamp_pl_interval()
2830 * Add an interval to a timestamp data type.
2831 * Note that interval has provisions for qualitative year/month and day
2832 * units, so try to do the right thing with them.
2833 * To add a month, increment the month, and use the same day of month.
2834 * Then, if the next month has fewer days, set the day of month
2835 * to the last day of month.
2836 * To add a day, increment the mday, and use the same time of day.
2837 * Lastly, add in the "quantitative time".
2838 */
2839 Datum
timestamp_pl_interval(PG_FUNCTION_ARGS)2840 timestamp_pl_interval(PG_FUNCTION_ARGS)
2841 {
2842 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2843 Interval *span = PG_GETARG_INTERVAL_P(1);
2844 Timestamp result;
2845
2846 if (TIMESTAMP_NOT_FINITE(timestamp))
2847 result = timestamp;
2848 else
2849 {
2850 if (span->month != 0)
2851 {
2852 struct pg_tm tt,
2853 *tm = &tt;
2854 fsec_t fsec;
2855
2856 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
2857 ereport(ERROR,
2858 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2859 errmsg("timestamp out of range")));
2860
2861 tm->tm_mon += span->month;
2862 if (tm->tm_mon > MONTHS_PER_YEAR)
2863 {
2864 tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
2865 tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
2866 }
2867 else if (tm->tm_mon < 1)
2868 {
2869 tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
2870 tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
2871 }
2872
2873 /* adjust for end of month boundary problems... */
2874 if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
2875 tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
2876
2877 if (tm2timestamp(tm, fsec, NULL, ×tamp) != 0)
2878 ereport(ERROR,
2879 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2880 errmsg("timestamp out of range")));
2881 }
2882
2883 if (span->day != 0)
2884 {
2885 struct pg_tm tt,
2886 *tm = &tt;
2887 fsec_t fsec;
2888 int julian;
2889
2890 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
2891 ereport(ERROR,
2892 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2893 errmsg("timestamp out of range")));
2894
2895 /* Add days by converting to and from Julian */
2896 julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
2897 j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
2898
2899 if (tm2timestamp(tm, fsec, NULL, ×tamp) != 0)
2900 ereport(ERROR,
2901 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2902 errmsg("timestamp out of range")));
2903 }
2904
2905 timestamp += span->time;
2906
2907 if (!IS_VALID_TIMESTAMP(timestamp))
2908 ereport(ERROR,
2909 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2910 errmsg("timestamp out of range")));
2911
2912 result = timestamp;
2913 }
2914
2915 PG_RETURN_TIMESTAMP(result);
2916 }
2917
2918 Datum
timestamp_mi_interval(PG_FUNCTION_ARGS)2919 timestamp_mi_interval(PG_FUNCTION_ARGS)
2920 {
2921 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2922 Interval *span = PG_GETARG_INTERVAL_P(1);
2923 Interval tspan;
2924
2925 tspan.month = -span->month;
2926 tspan.day = -span->day;
2927 tspan.time = -span->time;
2928
2929 return DirectFunctionCall2(timestamp_pl_interval,
2930 TimestampGetDatum(timestamp),
2931 PointerGetDatum(&tspan));
2932 }
2933
2934
2935 /* timestamptz_pl_interval()
2936 * Add an interval to a timestamp with time zone data type.
2937 * Note that interval has provisions for qualitative year/month
2938 * units, so try to do the right thing with them.
2939 * To add a month, increment the month, and use the same day of month.
2940 * Then, if the next month has fewer days, set the day of month
2941 * to the last day of month.
2942 * Lastly, add in the "quantitative time".
2943 */
2944 Datum
timestamptz_pl_interval(PG_FUNCTION_ARGS)2945 timestamptz_pl_interval(PG_FUNCTION_ARGS)
2946 {
2947 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
2948 Interval *span = PG_GETARG_INTERVAL_P(1);
2949 TimestampTz result;
2950 int tz;
2951
2952 if (TIMESTAMP_NOT_FINITE(timestamp))
2953 result = timestamp;
2954 else
2955 {
2956 if (span->month != 0)
2957 {
2958 struct pg_tm tt,
2959 *tm = &tt;
2960 fsec_t fsec;
2961
2962 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
2963 ereport(ERROR,
2964 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2965 errmsg("timestamp out of range")));
2966
2967 tm->tm_mon += span->month;
2968 if (tm->tm_mon > MONTHS_PER_YEAR)
2969 {
2970 tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
2971 tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
2972 }
2973 else if (tm->tm_mon < 1)
2974 {
2975 tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
2976 tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
2977 }
2978
2979 /* adjust for end of month boundary problems... */
2980 if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
2981 tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
2982
2983 tz = DetermineTimeZoneOffset(tm, session_timezone);
2984
2985 if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
2986 ereport(ERROR,
2987 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2988 errmsg("timestamp out of range")));
2989 }
2990
2991 if (span->day != 0)
2992 {
2993 struct pg_tm tt,
2994 *tm = &tt;
2995 fsec_t fsec;
2996 int julian;
2997
2998 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
2999 ereport(ERROR,
3000 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3001 errmsg("timestamp out of range")));
3002
3003 /* Add days by converting to and from Julian */
3004 julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
3005 j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
3006
3007 tz = DetermineTimeZoneOffset(tm, session_timezone);
3008
3009 if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
3010 ereport(ERROR,
3011 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3012 errmsg("timestamp out of range")));
3013 }
3014
3015 timestamp += span->time;
3016
3017 if (!IS_VALID_TIMESTAMP(timestamp))
3018 ereport(ERROR,
3019 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3020 errmsg("timestamp out of range")));
3021
3022 result = timestamp;
3023 }
3024
3025 PG_RETURN_TIMESTAMP(result);
3026 }
3027
3028 Datum
timestamptz_mi_interval(PG_FUNCTION_ARGS)3029 timestamptz_mi_interval(PG_FUNCTION_ARGS)
3030 {
3031 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
3032 Interval *span = PG_GETARG_INTERVAL_P(1);
3033 Interval tspan;
3034
3035 tspan.month = -span->month;
3036 tspan.day = -span->day;
3037 tspan.time = -span->time;
3038
3039 return DirectFunctionCall2(timestamptz_pl_interval,
3040 TimestampGetDatum(timestamp),
3041 PointerGetDatum(&tspan));
3042 }
3043
3044
3045 Datum
interval_um(PG_FUNCTION_ARGS)3046 interval_um(PG_FUNCTION_ARGS)
3047 {
3048 Interval *interval = PG_GETARG_INTERVAL_P(0);
3049 Interval *result;
3050
3051 result = (Interval *) palloc(sizeof(Interval));
3052
3053 result->time = -interval->time;
3054 /* overflow check copied from int4um */
3055 if (interval->time != 0 && SAMESIGN(result->time, interval->time))
3056 ereport(ERROR,
3057 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3058 errmsg("interval out of range")));
3059 result->day = -interval->day;
3060 if (interval->day != 0 && SAMESIGN(result->day, interval->day))
3061 ereport(ERROR,
3062 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3063 errmsg("interval out of range")));
3064 result->month = -interval->month;
3065 if (interval->month != 0 && SAMESIGN(result->month, interval->month))
3066 ereport(ERROR,
3067 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3068 errmsg("interval out of range")));
3069
3070 PG_RETURN_INTERVAL_P(result);
3071 }
3072
3073
3074 Datum
interval_smaller(PG_FUNCTION_ARGS)3075 interval_smaller(PG_FUNCTION_ARGS)
3076 {
3077 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
3078 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
3079 Interval *result;
3080
3081 /* use interval_cmp_internal to be sure this agrees with comparisons */
3082 if (interval_cmp_internal(interval1, interval2) < 0)
3083 result = interval1;
3084 else
3085 result = interval2;
3086 PG_RETURN_INTERVAL_P(result);
3087 }
3088
3089 Datum
interval_larger(PG_FUNCTION_ARGS)3090 interval_larger(PG_FUNCTION_ARGS)
3091 {
3092 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
3093 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
3094 Interval *result;
3095
3096 if (interval_cmp_internal(interval1, interval2) > 0)
3097 result = interval1;
3098 else
3099 result = interval2;
3100 PG_RETURN_INTERVAL_P(result);
3101 }
3102
3103 Datum
interval_pl(PG_FUNCTION_ARGS)3104 interval_pl(PG_FUNCTION_ARGS)
3105 {
3106 Interval *span1 = PG_GETARG_INTERVAL_P(0);
3107 Interval *span2 = PG_GETARG_INTERVAL_P(1);
3108 Interval *result;
3109
3110 result = (Interval *) palloc(sizeof(Interval));
3111
3112 result->month = span1->month + span2->month;
3113 /* overflow check copied from int4pl */
3114 if (SAMESIGN(span1->month, span2->month) &&
3115 !SAMESIGN(result->month, span1->month))
3116 ereport(ERROR,
3117 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3118 errmsg("interval out of range")));
3119
3120 result->day = span1->day + span2->day;
3121 if (SAMESIGN(span1->day, span2->day) &&
3122 !SAMESIGN(result->day, span1->day))
3123 ereport(ERROR,
3124 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3125 errmsg("interval out of range")));
3126
3127 result->time = span1->time + span2->time;
3128 if (SAMESIGN(span1->time, span2->time) &&
3129 !SAMESIGN(result->time, span1->time))
3130 ereport(ERROR,
3131 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3132 errmsg("interval out of range")));
3133
3134 PG_RETURN_INTERVAL_P(result);
3135 }
3136
3137 Datum
interval_mi(PG_FUNCTION_ARGS)3138 interval_mi(PG_FUNCTION_ARGS)
3139 {
3140 Interval *span1 = PG_GETARG_INTERVAL_P(0);
3141 Interval *span2 = PG_GETARG_INTERVAL_P(1);
3142 Interval *result;
3143
3144 result = (Interval *) palloc(sizeof(Interval));
3145
3146 result->month = span1->month - span2->month;
3147 /* overflow check copied from int4mi */
3148 if (!SAMESIGN(span1->month, span2->month) &&
3149 !SAMESIGN(result->month, span1->month))
3150 ereport(ERROR,
3151 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3152 errmsg("interval out of range")));
3153
3154 result->day = span1->day - span2->day;
3155 if (!SAMESIGN(span1->day, span2->day) &&
3156 !SAMESIGN(result->day, span1->day))
3157 ereport(ERROR,
3158 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3159 errmsg("interval out of range")));
3160
3161 result->time = span1->time - span2->time;
3162 if (!SAMESIGN(span1->time, span2->time) &&
3163 !SAMESIGN(result->time, span1->time))
3164 ereport(ERROR,
3165 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3166 errmsg("interval out of range")));
3167
3168 PG_RETURN_INTERVAL_P(result);
3169 }
3170
3171 /*
3172 * There is no interval_abs(): it is unclear what value to return:
3173 * http://archives.postgresql.org/pgsql-general/2009-10/msg01031.php
3174 * http://archives.postgresql.org/pgsql-general/2009-11/msg00041.php
3175 */
3176
3177 Datum
interval_mul(PG_FUNCTION_ARGS)3178 interval_mul(PG_FUNCTION_ARGS)
3179 {
3180 Interval *span = PG_GETARG_INTERVAL_P(0);
3181 float8 factor = PG_GETARG_FLOAT8(1);
3182 double month_remainder_days,
3183 sec_remainder,
3184 result_double;
3185 int32 orig_month = span->month,
3186 orig_day = span->day;
3187 Interval *result;
3188
3189 result = (Interval *) palloc(sizeof(Interval));
3190
3191 result_double = span->month * factor;
3192 if (isnan(result_double) ||
3193 result_double > INT_MAX || result_double < INT_MIN)
3194 ereport(ERROR,
3195 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3196 errmsg("interval out of range")));
3197 result->month = (int32) result_double;
3198
3199 result_double = span->day * factor;
3200 if (isnan(result_double) ||
3201 result_double > INT_MAX || result_double < INT_MIN)
3202 ereport(ERROR,
3203 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3204 errmsg("interval out of range")));
3205 result->day = (int32) result_double;
3206
3207 /*
3208 * The above correctly handles the whole-number part of the month and day
3209 * products, but we have to do something with any fractional part
3210 * resulting when the factor is non-integral. We cascade the fractions
3211 * down to lower units using the conversion factors DAYS_PER_MONTH and
3212 * SECS_PER_DAY. Note we do NOT cascade up, since we are not forced to do
3213 * so by the representation. The user can choose to cascade up later,
3214 * using justify_hours and/or justify_days.
3215 */
3216
3217 /*
3218 * Fractional months full days into days.
3219 *
3220 * Floating point calculation are inherently imprecise, so these
3221 * calculations are crafted to produce the most reliable result possible.
3222 * TSROUND() is needed to more accurately produce whole numbers where
3223 * appropriate.
3224 */
3225 month_remainder_days = (orig_month * factor - result->month) * DAYS_PER_MONTH;
3226 month_remainder_days = TSROUND(month_remainder_days);
3227 sec_remainder = (orig_day * factor - result->day +
3228 month_remainder_days - (int) month_remainder_days) * SECS_PER_DAY;
3229 sec_remainder = TSROUND(sec_remainder);
3230
3231 /*
3232 * Might have 24:00:00 hours due to rounding, or >24 hours because of time
3233 * cascade from months and days. It might still be >24 if the combination
3234 * of cascade and the seconds factor operation itself.
3235 */
3236 if (Abs(sec_remainder) >= SECS_PER_DAY)
3237 {
3238 result->day += (int) (sec_remainder / SECS_PER_DAY);
3239 sec_remainder -= (int) (sec_remainder / SECS_PER_DAY) * SECS_PER_DAY;
3240 }
3241
3242 /* cascade units down */
3243 result->day += (int32) month_remainder_days;
3244 result_double = rint(span->time * factor + sec_remainder * USECS_PER_SEC);
3245 if (isnan(result_double) || !FLOAT8_FITS_IN_INT64(result_double))
3246 ereport(ERROR,
3247 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3248 errmsg("interval out of range")));
3249 result->time = (int64) result_double;
3250
3251 PG_RETURN_INTERVAL_P(result);
3252 }
3253
3254 Datum
mul_d_interval(PG_FUNCTION_ARGS)3255 mul_d_interval(PG_FUNCTION_ARGS)
3256 {
3257 /* Args are float8 and Interval *, but leave them as generic Datum */
3258 Datum factor = PG_GETARG_DATUM(0);
3259 Datum span = PG_GETARG_DATUM(1);
3260
3261 return DirectFunctionCall2(interval_mul, span, factor);
3262 }
3263
3264 Datum
interval_div(PG_FUNCTION_ARGS)3265 interval_div(PG_FUNCTION_ARGS)
3266 {
3267 Interval *span = PG_GETARG_INTERVAL_P(0);
3268 float8 factor = PG_GETARG_FLOAT8(1);
3269 double month_remainder_days,
3270 sec_remainder;
3271 int32 orig_month = span->month,
3272 orig_day = span->day;
3273 Interval *result;
3274
3275 result = (Interval *) palloc(sizeof(Interval));
3276
3277 if (factor == 0.0)
3278 ereport(ERROR,
3279 (errcode(ERRCODE_DIVISION_BY_ZERO),
3280 errmsg("division by zero")));
3281
3282 result->month = (int32) (span->month / factor);
3283 result->day = (int32) (span->day / factor);
3284
3285 /*
3286 * Fractional months full days into days. See comment in interval_mul().
3287 */
3288 month_remainder_days = (orig_month / factor - result->month) * DAYS_PER_MONTH;
3289 month_remainder_days = TSROUND(month_remainder_days);
3290 sec_remainder = (orig_day / factor - result->day +
3291 month_remainder_days - (int) month_remainder_days) * SECS_PER_DAY;
3292 sec_remainder = TSROUND(sec_remainder);
3293 if (Abs(sec_remainder) >= SECS_PER_DAY)
3294 {
3295 result->day += (int) (sec_remainder / SECS_PER_DAY);
3296 sec_remainder -= (int) (sec_remainder / SECS_PER_DAY) * SECS_PER_DAY;
3297 }
3298
3299 /* cascade units down */
3300 result->day += (int32) month_remainder_days;
3301 result->time = rint(span->time / factor + sec_remainder * USECS_PER_SEC);
3302
3303 PG_RETURN_INTERVAL_P(result);
3304 }
3305
3306
3307 /*
3308 * in_range support functions for timestamps and intervals.
3309 *
3310 * Per SQL spec, we support these with interval as the offset type.
3311 * The spec's restriction that the offset not be negative is a bit hard to
3312 * decipher for intervals, but we choose to interpret it the same as our
3313 * interval comparison operators would.
3314 */
3315
3316 Datum
in_range_timestamptz_interval(PG_FUNCTION_ARGS)3317 in_range_timestamptz_interval(PG_FUNCTION_ARGS)
3318 {
3319 TimestampTz val = PG_GETARG_TIMESTAMPTZ(0);
3320 TimestampTz base = PG_GETARG_TIMESTAMPTZ(1);
3321 Interval *offset = PG_GETARG_INTERVAL_P(2);
3322 bool sub = PG_GETARG_BOOL(3);
3323 bool less = PG_GETARG_BOOL(4);
3324 TimestampTz sum;
3325
3326 if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3327 ereport(ERROR,
3328 (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3329 errmsg("invalid preceding or following size in window function")));
3330
3331 /* We don't currently bother to avoid overflow hazards here */
3332 if (sub)
3333 sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_mi_interval,
3334 TimestampTzGetDatum(base),
3335 IntervalPGetDatum(offset)));
3336 else
3337 sum = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,
3338 TimestampTzGetDatum(base),
3339 IntervalPGetDatum(offset)));
3340
3341 if (less)
3342 PG_RETURN_BOOL(val <= sum);
3343 else
3344 PG_RETURN_BOOL(val >= sum);
3345 }
3346
3347 Datum
in_range_timestamp_interval(PG_FUNCTION_ARGS)3348 in_range_timestamp_interval(PG_FUNCTION_ARGS)
3349 {
3350 Timestamp val = PG_GETARG_TIMESTAMP(0);
3351 Timestamp base = PG_GETARG_TIMESTAMP(1);
3352 Interval *offset = PG_GETARG_INTERVAL_P(2);
3353 bool sub = PG_GETARG_BOOL(3);
3354 bool less = PG_GETARG_BOOL(4);
3355 Timestamp sum;
3356
3357 if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3358 ereport(ERROR,
3359 (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3360 errmsg("invalid preceding or following size in window function")));
3361
3362 /* We don't currently bother to avoid overflow hazards here */
3363 if (sub)
3364 sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_mi_interval,
3365 TimestampGetDatum(base),
3366 IntervalPGetDatum(offset)));
3367 else
3368 sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_pl_interval,
3369 TimestampGetDatum(base),
3370 IntervalPGetDatum(offset)));
3371
3372 if (less)
3373 PG_RETURN_BOOL(val <= sum);
3374 else
3375 PG_RETURN_BOOL(val >= sum);
3376 }
3377
3378 Datum
in_range_interval_interval(PG_FUNCTION_ARGS)3379 in_range_interval_interval(PG_FUNCTION_ARGS)
3380 {
3381 Interval *val = PG_GETARG_INTERVAL_P(0);
3382 Interval *base = PG_GETARG_INTERVAL_P(1);
3383 Interval *offset = PG_GETARG_INTERVAL_P(2);
3384 bool sub = PG_GETARG_BOOL(3);
3385 bool less = PG_GETARG_BOOL(4);
3386 Interval *sum;
3387
3388 if (int128_compare(interval_cmp_value(offset), int64_to_int128(0)) < 0)
3389 ereport(ERROR,
3390 (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3391 errmsg("invalid preceding or following size in window function")));
3392
3393 /* We don't currently bother to avoid overflow hazards here */
3394 if (sub)
3395 sum = DatumGetIntervalP(DirectFunctionCall2(interval_mi,
3396 IntervalPGetDatum(base),
3397 IntervalPGetDatum(offset)));
3398 else
3399 sum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3400 IntervalPGetDatum(base),
3401 IntervalPGetDatum(offset)));
3402
3403 if (less)
3404 PG_RETURN_BOOL(interval_cmp_internal(val, sum) <= 0);
3405 else
3406 PG_RETURN_BOOL(interval_cmp_internal(val, sum) >= 0);
3407 }
3408
3409
3410 /*
3411 * interval_accum, interval_accum_inv, and interval_avg implement the
3412 * AVG(interval) aggregate.
3413 *
3414 * The transition datatype for this aggregate is a 2-element array of
3415 * intervals, where the first is the running sum and the second contains
3416 * the number of values so far in its 'time' field. This is a bit ugly
3417 * but it beats inventing a specialized datatype for the purpose.
3418 */
3419
3420 Datum
interval_accum(PG_FUNCTION_ARGS)3421 interval_accum(PG_FUNCTION_ARGS)
3422 {
3423 ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3424 Interval *newval = PG_GETARG_INTERVAL_P(1);
3425 Datum *transdatums;
3426 int ndatums;
3427 Interval sumX,
3428 N;
3429 Interval *newsum;
3430 ArrayType *result;
3431
3432 deconstruct_array(transarray,
3433 INTERVALOID, sizeof(Interval), false, 'd',
3434 &transdatums, NULL, &ndatums);
3435 if (ndatums != 2)
3436 elog(ERROR, "expected 2-element interval array");
3437
3438 sumX = *(DatumGetIntervalP(transdatums[0]));
3439 N = *(DatumGetIntervalP(transdatums[1]));
3440
3441 newsum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3442 IntervalPGetDatum(&sumX),
3443 IntervalPGetDatum(newval)));
3444 N.time += 1;
3445
3446 transdatums[0] = IntervalPGetDatum(newsum);
3447 transdatums[1] = IntervalPGetDatum(&N);
3448
3449 result = construct_array(transdatums, 2,
3450 INTERVALOID, sizeof(Interval), false, 'd');
3451
3452 PG_RETURN_ARRAYTYPE_P(result);
3453 }
3454
3455 Datum
interval_combine(PG_FUNCTION_ARGS)3456 interval_combine(PG_FUNCTION_ARGS)
3457 {
3458 ArrayType *transarray1 = PG_GETARG_ARRAYTYPE_P(0);
3459 ArrayType *transarray2 = PG_GETARG_ARRAYTYPE_P(1);
3460 Datum *transdatums1;
3461 Datum *transdatums2;
3462 int ndatums1;
3463 int ndatums2;
3464 Interval sum1,
3465 N1;
3466 Interval sum2,
3467 N2;
3468
3469 Interval *newsum;
3470 ArrayType *result;
3471
3472 deconstruct_array(transarray1,
3473 INTERVALOID, sizeof(Interval), false, 'd',
3474 &transdatums1, NULL, &ndatums1);
3475 if (ndatums1 != 2)
3476 elog(ERROR, "expected 2-element interval array");
3477
3478 sum1 = *(DatumGetIntervalP(transdatums1[0]));
3479 N1 = *(DatumGetIntervalP(transdatums1[1]));
3480
3481 deconstruct_array(transarray2,
3482 INTERVALOID, sizeof(Interval), false, 'd',
3483 &transdatums2, NULL, &ndatums2);
3484 if (ndatums2 != 2)
3485 elog(ERROR, "expected 2-element interval array");
3486
3487 sum2 = *(DatumGetIntervalP(transdatums2[0]));
3488 N2 = *(DatumGetIntervalP(transdatums2[1]));
3489
3490 newsum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3491 IntervalPGetDatum(&sum1),
3492 IntervalPGetDatum(&sum2)));
3493 N1.time += N2.time;
3494
3495 transdatums1[0] = IntervalPGetDatum(newsum);
3496 transdatums1[1] = IntervalPGetDatum(&N1);
3497
3498 result = construct_array(transdatums1, 2,
3499 INTERVALOID, sizeof(Interval), false, 'd');
3500
3501 PG_RETURN_ARRAYTYPE_P(result);
3502 }
3503
3504 Datum
interval_accum_inv(PG_FUNCTION_ARGS)3505 interval_accum_inv(PG_FUNCTION_ARGS)
3506 {
3507 ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3508 Interval *newval = PG_GETARG_INTERVAL_P(1);
3509 Datum *transdatums;
3510 int ndatums;
3511 Interval sumX,
3512 N;
3513 Interval *newsum;
3514 ArrayType *result;
3515
3516 deconstruct_array(transarray,
3517 INTERVALOID, sizeof(Interval), false, 'd',
3518 &transdatums, NULL, &ndatums);
3519 if (ndatums != 2)
3520 elog(ERROR, "expected 2-element interval array");
3521
3522 sumX = *(DatumGetIntervalP(transdatums[0]));
3523 N = *(DatumGetIntervalP(transdatums[1]));
3524
3525 newsum = DatumGetIntervalP(DirectFunctionCall2(interval_mi,
3526 IntervalPGetDatum(&sumX),
3527 IntervalPGetDatum(newval)));
3528 N.time -= 1;
3529
3530 transdatums[0] = IntervalPGetDatum(newsum);
3531 transdatums[1] = IntervalPGetDatum(&N);
3532
3533 result = construct_array(transdatums, 2,
3534 INTERVALOID, sizeof(Interval), false, 'd');
3535
3536 PG_RETURN_ARRAYTYPE_P(result);
3537 }
3538
3539 Datum
interval_avg(PG_FUNCTION_ARGS)3540 interval_avg(PG_FUNCTION_ARGS)
3541 {
3542 ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
3543 Datum *transdatums;
3544 int ndatums;
3545 Interval sumX,
3546 N;
3547
3548 deconstruct_array(transarray,
3549 INTERVALOID, sizeof(Interval), false, 'd',
3550 &transdatums, NULL, &ndatums);
3551 if (ndatums != 2)
3552 elog(ERROR, "expected 2-element interval array");
3553
3554 sumX = *(DatumGetIntervalP(transdatums[0]));
3555 N = *(DatumGetIntervalP(transdatums[1]));
3556
3557 /* SQL defines AVG of no values to be NULL */
3558 if (N.time == 0)
3559 PG_RETURN_NULL();
3560
3561 return DirectFunctionCall2(interval_div,
3562 IntervalPGetDatum(&sumX),
3563 Float8GetDatum((double) N.time));
3564 }
3565
3566
3567 /* timestamp_age()
3568 * Calculate time difference while retaining year/month fields.
3569 * Note that this does not result in an accurate absolute time span
3570 * since year and month are out of context once the arithmetic
3571 * is done.
3572 */
3573 Datum
timestamp_age(PG_FUNCTION_ARGS)3574 timestamp_age(PG_FUNCTION_ARGS)
3575 {
3576 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
3577 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
3578 Interval *result;
3579 fsec_t fsec,
3580 fsec1,
3581 fsec2;
3582 struct pg_tm tt,
3583 *tm = &tt;
3584 struct pg_tm tt1,
3585 *tm1 = &tt1;
3586 struct pg_tm tt2,
3587 *tm2 = &tt2;
3588
3589 result = (Interval *) palloc(sizeof(Interval));
3590
3591 if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
3592 timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
3593 {
3594 /* form the symbolic difference */
3595 fsec = fsec1 - fsec2;
3596 tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
3597 tm->tm_min = tm1->tm_min - tm2->tm_min;
3598 tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
3599 tm->tm_mday = tm1->tm_mday - tm2->tm_mday;
3600 tm->tm_mon = tm1->tm_mon - tm2->tm_mon;
3601 tm->tm_year = tm1->tm_year - tm2->tm_year;
3602
3603 /* flip sign if necessary... */
3604 if (dt1 < dt2)
3605 {
3606 fsec = -fsec;
3607 tm->tm_sec = -tm->tm_sec;
3608 tm->tm_min = -tm->tm_min;
3609 tm->tm_hour = -tm->tm_hour;
3610 tm->tm_mday = -tm->tm_mday;
3611 tm->tm_mon = -tm->tm_mon;
3612 tm->tm_year = -tm->tm_year;
3613 }
3614
3615 /* propagate any negative fields into the next higher field */
3616 while (fsec < 0)
3617 {
3618 fsec += USECS_PER_SEC;
3619 tm->tm_sec--;
3620 }
3621
3622 while (tm->tm_sec < 0)
3623 {
3624 tm->tm_sec += SECS_PER_MINUTE;
3625 tm->tm_min--;
3626 }
3627
3628 while (tm->tm_min < 0)
3629 {
3630 tm->tm_min += MINS_PER_HOUR;
3631 tm->tm_hour--;
3632 }
3633
3634 while (tm->tm_hour < 0)
3635 {
3636 tm->tm_hour += HOURS_PER_DAY;
3637 tm->tm_mday--;
3638 }
3639
3640 while (tm->tm_mday < 0)
3641 {
3642 if (dt1 < dt2)
3643 {
3644 tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
3645 tm->tm_mon--;
3646 }
3647 else
3648 {
3649 tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
3650 tm->tm_mon--;
3651 }
3652 }
3653
3654 while (tm->tm_mon < 0)
3655 {
3656 tm->tm_mon += MONTHS_PER_YEAR;
3657 tm->tm_year--;
3658 }
3659
3660 /* recover sign if necessary... */
3661 if (dt1 < dt2)
3662 {
3663 fsec = -fsec;
3664 tm->tm_sec = -tm->tm_sec;
3665 tm->tm_min = -tm->tm_min;
3666 tm->tm_hour = -tm->tm_hour;
3667 tm->tm_mday = -tm->tm_mday;
3668 tm->tm_mon = -tm->tm_mon;
3669 tm->tm_year = -tm->tm_year;
3670 }
3671
3672 if (tm2interval(tm, fsec, result) != 0)
3673 ereport(ERROR,
3674 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3675 errmsg("interval out of range")));
3676 }
3677 else
3678 ereport(ERROR,
3679 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3680 errmsg("timestamp out of range")));
3681
3682 PG_RETURN_INTERVAL_P(result);
3683 }
3684
3685
3686 /* timestamptz_age()
3687 * Calculate time difference while retaining year/month fields.
3688 * Note that this does not result in an accurate absolute time span
3689 * since year and month are out of context once the arithmetic
3690 * is done.
3691 */
3692 Datum
timestamptz_age(PG_FUNCTION_ARGS)3693 timestamptz_age(PG_FUNCTION_ARGS)
3694 {
3695 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
3696 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
3697 Interval *result;
3698 fsec_t fsec,
3699 fsec1,
3700 fsec2;
3701 struct pg_tm tt,
3702 *tm = &tt;
3703 struct pg_tm tt1,
3704 *tm1 = &tt1;
3705 struct pg_tm tt2,
3706 *tm2 = &tt2;
3707 int tz1;
3708 int tz2;
3709
3710 result = (Interval *) palloc(sizeof(Interval));
3711
3712 if (timestamp2tm(dt1, &tz1, tm1, &fsec1, NULL, NULL) == 0 &&
3713 timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0)
3714 {
3715 /* form the symbolic difference */
3716 fsec = fsec1 - fsec2;
3717 tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
3718 tm->tm_min = tm1->tm_min - tm2->tm_min;
3719 tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
3720 tm->tm_mday = tm1->tm_mday - tm2->tm_mday;
3721 tm->tm_mon = tm1->tm_mon - tm2->tm_mon;
3722 tm->tm_year = tm1->tm_year - tm2->tm_year;
3723
3724 /* flip sign if necessary... */
3725 if (dt1 < dt2)
3726 {
3727 fsec = -fsec;
3728 tm->tm_sec = -tm->tm_sec;
3729 tm->tm_min = -tm->tm_min;
3730 tm->tm_hour = -tm->tm_hour;
3731 tm->tm_mday = -tm->tm_mday;
3732 tm->tm_mon = -tm->tm_mon;
3733 tm->tm_year = -tm->tm_year;
3734 }
3735
3736 /* propagate any negative fields into the next higher field */
3737 while (fsec < 0)
3738 {
3739 fsec += USECS_PER_SEC;
3740 tm->tm_sec--;
3741 }
3742
3743 while (tm->tm_sec < 0)
3744 {
3745 tm->tm_sec += SECS_PER_MINUTE;
3746 tm->tm_min--;
3747 }
3748
3749 while (tm->tm_min < 0)
3750 {
3751 tm->tm_min += MINS_PER_HOUR;
3752 tm->tm_hour--;
3753 }
3754
3755 while (tm->tm_hour < 0)
3756 {
3757 tm->tm_hour += HOURS_PER_DAY;
3758 tm->tm_mday--;
3759 }
3760
3761 while (tm->tm_mday < 0)
3762 {
3763 if (dt1 < dt2)
3764 {
3765 tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
3766 tm->tm_mon--;
3767 }
3768 else
3769 {
3770 tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
3771 tm->tm_mon--;
3772 }
3773 }
3774
3775 while (tm->tm_mon < 0)
3776 {
3777 tm->tm_mon += MONTHS_PER_YEAR;
3778 tm->tm_year--;
3779 }
3780
3781 /*
3782 * Note: we deliberately ignore any difference between tz1 and tz2.
3783 */
3784
3785 /* recover sign if necessary... */
3786 if (dt1 < dt2)
3787 {
3788 fsec = -fsec;
3789 tm->tm_sec = -tm->tm_sec;
3790 tm->tm_min = -tm->tm_min;
3791 tm->tm_hour = -tm->tm_hour;
3792 tm->tm_mday = -tm->tm_mday;
3793 tm->tm_mon = -tm->tm_mon;
3794 tm->tm_year = -tm->tm_year;
3795 }
3796
3797 if (tm2interval(tm, fsec, result) != 0)
3798 ereport(ERROR,
3799 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3800 errmsg("interval out of range")));
3801 }
3802 else
3803 ereport(ERROR,
3804 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3805 errmsg("timestamp out of range")));
3806
3807 PG_RETURN_INTERVAL_P(result);
3808 }
3809
3810
3811 /*----------------------------------------------------------
3812 * Conversion operators.
3813 *---------------------------------------------------------*/
3814
3815
3816 /* timestamp_trunc()
3817 * Truncate timestamp to specified units.
3818 */
3819 Datum
timestamp_trunc(PG_FUNCTION_ARGS)3820 timestamp_trunc(PG_FUNCTION_ARGS)
3821 {
3822 text *units = PG_GETARG_TEXT_PP(0);
3823 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
3824 Timestamp result;
3825 int type,
3826 val;
3827 char *lowunits;
3828 fsec_t fsec;
3829 struct pg_tm tt,
3830 *tm = &tt;
3831
3832 if (TIMESTAMP_NOT_FINITE(timestamp))
3833 PG_RETURN_TIMESTAMP(timestamp);
3834
3835 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
3836 VARSIZE_ANY_EXHDR(units),
3837 false);
3838
3839 type = DecodeUnits(0, lowunits, &val);
3840
3841 if (type == UNITS)
3842 {
3843 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
3844 ereport(ERROR,
3845 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3846 errmsg("timestamp out of range")));
3847
3848 switch (val)
3849 {
3850 case DTK_WEEK:
3851 {
3852 int woy;
3853
3854 woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
3855
3856 /*
3857 * If it is week 52/53 and the month is January, then the
3858 * week must belong to the previous year. Also, some
3859 * December dates belong to the next year.
3860 */
3861 if (woy >= 52 && tm->tm_mon == 1)
3862 --tm->tm_year;
3863 if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
3864 ++tm->tm_year;
3865 isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
3866 tm->tm_hour = 0;
3867 tm->tm_min = 0;
3868 tm->tm_sec = 0;
3869 fsec = 0;
3870 break;
3871 }
3872 case DTK_MILLENNIUM:
3873 /* see comments in timestamptz_trunc */
3874 if (tm->tm_year > 0)
3875 tm->tm_year = ((tm->tm_year + 999) / 1000) * 1000 - 999;
3876 else
3877 tm->tm_year = -((999 - (tm->tm_year - 1)) / 1000) * 1000 + 1;
3878 /* FALL THRU */
3879 case DTK_CENTURY:
3880 /* see comments in timestamptz_trunc */
3881 if (tm->tm_year > 0)
3882 tm->tm_year = ((tm->tm_year + 99) / 100) * 100 - 99;
3883 else
3884 tm->tm_year = -((99 - (tm->tm_year - 1)) / 100) * 100 + 1;
3885 /* FALL THRU */
3886 case DTK_DECADE:
3887 /* see comments in timestamptz_trunc */
3888 if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
3889 {
3890 if (tm->tm_year > 0)
3891 tm->tm_year = (tm->tm_year / 10) * 10;
3892 else
3893 tm->tm_year = -((8 - (tm->tm_year - 1)) / 10) * 10;
3894 }
3895 /* FALL THRU */
3896 case DTK_YEAR:
3897 tm->tm_mon = 1;
3898 /* FALL THRU */
3899 case DTK_QUARTER:
3900 tm->tm_mon = (3 * ((tm->tm_mon - 1) / 3)) + 1;
3901 /* FALL THRU */
3902 case DTK_MONTH:
3903 tm->tm_mday = 1;
3904 /* FALL THRU */
3905 case DTK_DAY:
3906 tm->tm_hour = 0;
3907 /* FALL THRU */
3908 case DTK_HOUR:
3909 tm->tm_min = 0;
3910 /* FALL THRU */
3911 case DTK_MINUTE:
3912 tm->tm_sec = 0;
3913 /* FALL THRU */
3914 case DTK_SECOND:
3915 fsec = 0;
3916 break;
3917
3918 case DTK_MILLISEC:
3919 fsec = (fsec / 1000) * 1000;
3920 break;
3921
3922 case DTK_MICROSEC:
3923 break;
3924
3925 default:
3926 ereport(ERROR,
3927 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
3928 errmsg("timestamp units \"%s\" not supported",
3929 lowunits)));
3930 result = 0;
3931 }
3932
3933 if (tm2timestamp(tm, fsec, NULL, &result) != 0)
3934 ereport(ERROR,
3935 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3936 errmsg("timestamp out of range")));
3937 }
3938 else
3939 {
3940 ereport(ERROR,
3941 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
3942 errmsg("timestamp units \"%s\" not recognized",
3943 lowunits)));
3944 result = 0;
3945 }
3946
3947 PG_RETURN_TIMESTAMP(result);
3948 }
3949
3950 /*
3951 * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
3952 *
3953 * tzp identifies the zone to truncate with respect to. We assume
3954 * infinite timestamps have already been rejected.
3955 */
3956 static TimestampTz
timestamptz_trunc_internal(text * units,TimestampTz timestamp,pg_tz * tzp)3957 timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
3958 {
3959 TimestampTz result;
3960 int tz;
3961 int type,
3962 val;
3963 bool redotz = false;
3964 char *lowunits;
3965 fsec_t fsec;
3966 struct pg_tm tt,
3967 *tm = &tt;
3968
3969 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
3970 VARSIZE_ANY_EXHDR(units),
3971 false);
3972
3973 type = DecodeUnits(0, lowunits, &val);
3974
3975 if (type == UNITS)
3976 {
3977 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, tzp) != 0)
3978 ereport(ERROR,
3979 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3980 errmsg("timestamp out of range")));
3981
3982 switch (val)
3983 {
3984 case DTK_WEEK:
3985 {
3986 int woy;
3987
3988 woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
3989
3990 /*
3991 * If it is week 52/53 and the month is January, then the
3992 * week must belong to the previous year. Also, some
3993 * December dates belong to the next year.
3994 */
3995 if (woy >= 52 && tm->tm_mon == 1)
3996 --tm->tm_year;
3997 if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
3998 ++tm->tm_year;
3999 isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
4000 tm->tm_hour = 0;
4001 tm->tm_min = 0;
4002 tm->tm_sec = 0;
4003 fsec = 0;
4004 redotz = true;
4005 break;
4006 }
4007 /* one may consider DTK_THOUSAND and DTK_HUNDRED... */
4008 case DTK_MILLENNIUM:
4009
4010 /*
4011 * truncating to the millennium? what is this supposed to
4012 * mean? let us put the first year of the millennium... i.e.
4013 * -1000, 1, 1001, 2001...
4014 */
4015 if (tm->tm_year > 0)
4016 tm->tm_year = ((tm->tm_year + 999) / 1000) * 1000 - 999;
4017 else
4018 tm->tm_year = -((999 - (tm->tm_year - 1)) / 1000) * 1000 + 1;
4019 /* FALL THRU */
4020 case DTK_CENTURY:
4021 /* truncating to the century? as above: -100, 1, 101... */
4022 if (tm->tm_year > 0)
4023 tm->tm_year = ((tm->tm_year + 99) / 100) * 100 - 99;
4024 else
4025 tm->tm_year = -((99 - (tm->tm_year - 1)) / 100) * 100 + 1;
4026 /* FALL THRU */
4027 case DTK_DECADE:
4028
4029 /*
4030 * truncating to the decade? first year of the decade. must
4031 * not be applied if year was truncated before!
4032 */
4033 if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
4034 {
4035 if (tm->tm_year > 0)
4036 tm->tm_year = (tm->tm_year / 10) * 10;
4037 else
4038 tm->tm_year = -((8 - (tm->tm_year - 1)) / 10) * 10;
4039 }
4040 /* FALL THRU */
4041 case DTK_YEAR:
4042 tm->tm_mon = 1;
4043 /* FALL THRU */
4044 case DTK_QUARTER:
4045 tm->tm_mon = (3 * ((tm->tm_mon - 1) / 3)) + 1;
4046 /* FALL THRU */
4047 case DTK_MONTH:
4048 tm->tm_mday = 1;
4049 /* FALL THRU */
4050 case DTK_DAY:
4051 tm->tm_hour = 0;
4052 redotz = true; /* for all cases >= DAY */
4053 /* FALL THRU */
4054 case DTK_HOUR:
4055 tm->tm_min = 0;
4056 /* FALL THRU */
4057 case DTK_MINUTE:
4058 tm->tm_sec = 0;
4059 /* FALL THRU */
4060 case DTK_SECOND:
4061 fsec = 0;
4062 break;
4063 case DTK_MILLISEC:
4064 fsec = (fsec / 1000) * 1000;
4065 break;
4066 case DTK_MICROSEC:
4067 break;
4068
4069 default:
4070 ereport(ERROR,
4071 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4072 errmsg("timestamp with time zone units \"%s\" not "
4073 "supported", lowunits)));
4074 result = 0;
4075 }
4076
4077 if (redotz)
4078 tz = DetermineTimeZoneOffset(tm, tzp);
4079
4080 if (tm2timestamp(tm, fsec, &tz, &result) != 0)
4081 ereport(ERROR,
4082 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4083 errmsg("timestamp out of range")));
4084 }
4085 else
4086 {
4087 ereport(ERROR,
4088 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4089 errmsg("timestamp with time zone units \"%s\" not recognized",
4090 lowunits)));
4091 result = 0;
4092 }
4093
4094 return result;
4095 }
4096
4097 /* timestamptz_trunc()
4098 * Truncate timestamptz to specified units in session timezone.
4099 */
4100 Datum
timestamptz_trunc(PG_FUNCTION_ARGS)4101 timestamptz_trunc(PG_FUNCTION_ARGS)
4102 {
4103 text *units = PG_GETARG_TEXT_PP(0);
4104 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4105 TimestampTz result;
4106
4107 if (TIMESTAMP_NOT_FINITE(timestamp))
4108 PG_RETURN_TIMESTAMPTZ(timestamp);
4109
4110 result = timestamptz_trunc_internal(units, timestamp, session_timezone);
4111
4112 PG_RETURN_TIMESTAMPTZ(result);
4113 }
4114
4115 /* timestamptz_trunc_zone()
4116 * Truncate timestamptz to specified units in specified timezone.
4117 */
4118 Datum
timestamptz_trunc_zone(PG_FUNCTION_ARGS)4119 timestamptz_trunc_zone(PG_FUNCTION_ARGS)
4120 {
4121 text *units = PG_GETARG_TEXT_PP(0);
4122 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4123 text *zone = PG_GETARG_TEXT_PP(2);
4124 TimestampTz result;
4125 char tzname[TZ_STRLEN_MAX + 1];
4126 char *lowzone;
4127 int type,
4128 val;
4129 pg_tz *tzp;
4130
4131 /*
4132 * timestamptz_zone() doesn't look up the zone for infinite inputs, so we
4133 * don't do so here either.
4134 */
4135 if (TIMESTAMP_NOT_FINITE(timestamp))
4136 PG_RETURN_TIMESTAMP(timestamp);
4137
4138 /*
4139 * Look up the requested timezone (see notes in timestamptz_zone()).
4140 */
4141 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
4142
4143 /* DecodeTimezoneAbbrev requires lowercase input */
4144 lowzone = downcase_truncate_identifier(tzname,
4145 strlen(tzname),
4146 false);
4147
4148 type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
4149
4150 if (type == TZ || type == DTZ)
4151 {
4152 /* fixed-offset abbreviation, get a pg_tz descriptor for that */
4153 tzp = pg_tzset_offset(-val);
4154 }
4155 else if (type == DYNTZ)
4156 {
4157 /* dynamic-offset abbreviation, use its referenced timezone */
4158 }
4159 else
4160 {
4161 /* try it as a full zone name */
4162 tzp = pg_tzset(tzname);
4163 if (!tzp)
4164 ereport(ERROR,
4165 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4166 errmsg("time zone \"%s\" not recognized", tzname)));
4167 }
4168
4169 result = timestamptz_trunc_internal(units, timestamp, tzp);
4170
4171 PG_RETURN_TIMESTAMPTZ(result);
4172 }
4173
4174 /* interval_trunc()
4175 * Extract specified field from interval.
4176 */
4177 Datum
interval_trunc(PG_FUNCTION_ARGS)4178 interval_trunc(PG_FUNCTION_ARGS)
4179 {
4180 text *units = PG_GETARG_TEXT_PP(0);
4181 Interval *interval = PG_GETARG_INTERVAL_P(1);
4182 Interval *result;
4183 int type,
4184 val;
4185 char *lowunits;
4186 fsec_t fsec;
4187 struct pg_tm tt,
4188 *tm = &tt;
4189
4190 result = (Interval *) palloc(sizeof(Interval));
4191
4192 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4193 VARSIZE_ANY_EXHDR(units),
4194 false);
4195
4196 type = DecodeUnits(0, lowunits, &val);
4197
4198 if (type == UNITS)
4199 {
4200 if (interval2tm(*interval, tm, &fsec) == 0)
4201 {
4202 switch (val)
4203 {
4204 case DTK_MILLENNIUM:
4205 /* caution: C division may have negative remainder */
4206 tm->tm_year = (tm->tm_year / 1000) * 1000;
4207 /* FALL THRU */
4208 case DTK_CENTURY:
4209 /* caution: C division may have negative remainder */
4210 tm->tm_year = (tm->tm_year / 100) * 100;
4211 /* FALL THRU */
4212 case DTK_DECADE:
4213 /* caution: C division may have negative remainder */
4214 tm->tm_year = (tm->tm_year / 10) * 10;
4215 /* FALL THRU */
4216 case DTK_YEAR:
4217 tm->tm_mon = 0;
4218 /* FALL THRU */
4219 case DTK_QUARTER:
4220 tm->tm_mon = 3 * (tm->tm_mon / 3);
4221 /* FALL THRU */
4222 case DTK_MONTH:
4223 tm->tm_mday = 0;
4224 /* FALL THRU */
4225 case DTK_DAY:
4226 tm->tm_hour = 0;
4227 /* FALL THRU */
4228 case DTK_HOUR:
4229 tm->tm_min = 0;
4230 /* FALL THRU */
4231 case DTK_MINUTE:
4232 tm->tm_sec = 0;
4233 /* FALL THRU */
4234 case DTK_SECOND:
4235 fsec = 0;
4236 break;
4237 case DTK_MILLISEC:
4238 fsec = (fsec / 1000) * 1000;
4239 break;
4240 case DTK_MICROSEC:
4241 break;
4242
4243 default:
4244 if (val == DTK_WEEK)
4245 ereport(ERROR,
4246 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4247 errmsg("interval units \"%s\" not supported "
4248 "because months usually have fractional weeks",
4249 lowunits)));
4250 else
4251 ereport(ERROR,
4252 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4253 errmsg("interval units \"%s\" not supported",
4254 lowunits)));
4255 }
4256
4257 if (tm2interval(tm, fsec, result) != 0)
4258 ereport(ERROR,
4259 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4260 errmsg("interval out of range")));
4261 }
4262 else
4263 elog(ERROR, "could not convert interval to tm");
4264 }
4265 else
4266 {
4267 ereport(ERROR,
4268 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4269 errmsg("interval units \"%s\" not recognized",
4270 lowunits)));
4271 }
4272
4273 PG_RETURN_INTERVAL_P(result);
4274 }
4275
4276 /* isoweek2j()
4277 *
4278 * Return the Julian day which corresponds to the first day (Monday) of the given ISO 8601 year and week.
4279 * Julian days are used to convert between ISO week dates and Gregorian dates.
4280 */
4281 int
isoweek2j(int year,int week)4282 isoweek2j(int year, int week)
4283 {
4284 int day0,
4285 day4;
4286
4287 /* fourth day of current year */
4288 day4 = date2j(year, 1, 4);
4289
4290 /* day0 == offset to first day of week (Monday) */
4291 day0 = j2day(day4 - 1);
4292
4293 return ((week - 1) * 7) + (day4 - day0);
4294 }
4295
4296 /* isoweek2date()
4297 * Convert ISO week of year number to date.
4298 * The year field must be specified with the ISO year!
4299 * karel 2000/08/07
4300 */
4301 void
isoweek2date(int woy,int * year,int * mon,int * mday)4302 isoweek2date(int woy, int *year, int *mon, int *mday)
4303 {
4304 j2date(isoweek2j(*year, woy), year, mon, mday);
4305 }
4306
4307 /* isoweekdate2date()
4308 *
4309 * Convert an ISO 8601 week date (ISO year, ISO week) into a Gregorian date.
4310 * Gregorian day of week sent so weekday strings can be supplied.
4311 * Populates year, mon, and mday with the correct Gregorian values.
4312 * year must be passed in as the ISO year.
4313 */
4314 void
isoweekdate2date(int isoweek,int wday,int * year,int * mon,int * mday)4315 isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday)
4316 {
4317 int jday;
4318
4319 jday = isoweek2j(*year, isoweek);
4320 /* convert Gregorian week start (Sunday=1) to ISO week start (Monday=1) */
4321 if (wday > 1)
4322 jday += wday - 2;
4323 else
4324 jday += 6;
4325 j2date(jday, year, mon, mday);
4326 }
4327
4328 /* date2isoweek()
4329 *
4330 * Returns ISO week number of year.
4331 */
4332 int
date2isoweek(int year,int mon,int mday)4333 date2isoweek(int year, int mon, int mday)
4334 {
4335 float8 result;
4336 int day0,
4337 day4,
4338 dayn;
4339
4340 /* current day */
4341 dayn = date2j(year, mon, mday);
4342
4343 /* fourth day of current year */
4344 day4 = date2j(year, 1, 4);
4345
4346 /* day0 == offset to first day of week (Monday) */
4347 day0 = j2day(day4 - 1);
4348
4349 /*
4350 * We need the first week containing a Thursday, otherwise this day falls
4351 * into the previous year for purposes of counting weeks
4352 */
4353 if (dayn < day4 - day0)
4354 {
4355 day4 = date2j(year - 1, 1, 4);
4356
4357 /* day0 == offset to first day of week (Monday) */
4358 day0 = j2day(day4 - 1);
4359 }
4360
4361 result = (dayn - (day4 - day0)) / 7 + 1;
4362
4363 /*
4364 * Sometimes the last few days in a year will fall into the first week of
4365 * the next year, so check for this.
4366 */
4367 if (result >= 52)
4368 {
4369 day4 = date2j(year + 1, 1, 4);
4370
4371 /* day0 == offset to first day of week (Monday) */
4372 day0 = j2day(day4 - 1);
4373
4374 if (dayn >= day4 - day0)
4375 result = (dayn - (day4 - day0)) / 7 + 1;
4376 }
4377
4378 return (int) result;
4379 }
4380
4381
4382 /* date2isoyear()
4383 *
4384 * Returns ISO 8601 year number.
4385 * Note: zero or negative results follow the year-zero-exists convention.
4386 */
4387 int
date2isoyear(int year,int mon,int mday)4388 date2isoyear(int year, int mon, int mday)
4389 {
4390 float8 result;
4391 int day0,
4392 day4,
4393 dayn;
4394
4395 /* current day */
4396 dayn = date2j(year, mon, mday);
4397
4398 /* fourth day of current year */
4399 day4 = date2j(year, 1, 4);
4400
4401 /* day0 == offset to first day of week (Monday) */
4402 day0 = j2day(day4 - 1);
4403
4404 /*
4405 * We need the first week containing a Thursday, otherwise this day falls
4406 * into the previous year for purposes of counting weeks
4407 */
4408 if (dayn < day4 - day0)
4409 {
4410 day4 = date2j(year - 1, 1, 4);
4411
4412 /* day0 == offset to first day of week (Monday) */
4413 day0 = j2day(day4 - 1);
4414
4415 year--;
4416 }
4417
4418 result = (dayn - (day4 - day0)) / 7 + 1;
4419
4420 /*
4421 * Sometimes the last few days in a year will fall into the first week of
4422 * the next year, so check for this.
4423 */
4424 if (result >= 52)
4425 {
4426 day4 = date2j(year + 1, 1, 4);
4427
4428 /* day0 == offset to first day of week (Monday) */
4429 day0 = j2day(day4 - 1);
4430
4431 if (dayn >= day4 - day0)
4432 year++;
4433 }
4434
4435 return year;
4436 }
4437
4438
4439 /* date2isoyearday()
4440 *
4441 * Returns the ISO 8601 day-of-year, given a Gregorian year, month and day.
4442 * Possible return values are 1 through 371 (364 in non-leap years).
4443 */
4444 int
date2isoyearday(int year,int mon,int mday)4445 date2isoyearday(int year, int mon, int mday)
4446 {
4447 return date2j(year, mon, mday) - isoweek2j(date2isoyear(year, mon, mday), 1) + 1;
4448 }
4449
4450 /*
4451 * NonFiniteTimestampTzPart
4452 *
4453 * Used by timestamp_part and timestamptz_part when extracting from infinite
4454 * timestamp[tz]. Returns +/-Infinity if that is the appropriate result,
4455 * otherwise returns zero (which should be taken as meaning to return NULL).
4456 *
4457 * Errors thrown here for invalid units should exactly match those that
4458 * would be thrown in the calling functions, else there will be unexpected
4459 * discrepancies between finite- and infinite-input cases.
4460 */
4461 static float8
NonFiniteTimestampTzPart(int type,int unit,char * lowunits,bool isNegative,bool isTz)4462 NonFiniteTimestampTzPart(int type, int unit, char *lowunits,
4463 bool isNegative, bool isTz)
4464 {
4465 if ((type != UNITS) && (type != RESERV))
4466 {
4467 if (isTz)
4468 ereport(ERROR,
4469 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4470 errmsg("timestamp with time zone units \"%s\" not recognized",
4471 lowunits)));
4472 else
4473 ereport(ERROR,
4474 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4475 errmsg("timestamp units \"%s\" not recognized",
4476 lowunits)));
4477 }
4478
4479 switch (unit)
4480 {
4481 /* Oscillating units */
4482 case DTK_MICROSEC:
4483 case DTK_MILLISEC:
4484 case DTK_SECOND:
4485 case DTK_MINUTE:
4486 case DTK_HOUR:
4487 case DTK_DAY:
4488 case DTK_MONTH:
4489 case DTK_QUARTER:
4490 case DTK_WEEK:
4491 case DTK_DOW:
4492 case DTK_ISODOW:
4493 case DTK_DOY:
4494 case DTK_TZ:
4495 case DTK_TZ_MINUTE:
4496 case DTK_TZ_HOUR:
4497 return 0.0;
4498
4499 /* Monotonically-increasing units */
4500 case DTK_YEAR:
4501 case DTK_DECADE:
4502 case DTK_CENTURY:
4503 case DTK_MILLENNIUM:
4504 case DTK_JULIAN:
4505 case DTK_ISOYEAR:
4506 case DTK_EPOCH:
4507 if (isNegative)
4508 return -get_float8_infinity();
4509 else
4510 return get_float8_infinity();
4511
4512 default:
4513 if (isTz)
4514 ereport(ERROR,
4515 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4516 errmsg("timestamp with time zone units \"%s\" not supported",
4517 lowunits)));
4518 else
4519 ereport(ERROR,
4520 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4521 errmsg("timestamp units \"%s\" not supported",
4522 lowunits)));
4523 return 0.0; /* keep compiler quiet */
4524 }
4525 }
4526
4527 /* timestamp_part()
4528 * Extract specified field from timestamp.
4529 */
4530 Datum
timestamp_part(PG_FUNCTION_ARGS)4531 timestamp_part(PG_FUNCTION_ARGS)
4532 {
4533 text *units = PG_GETARG_TEXT_PP(0);
4534 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
4535 float8 result;
4536 Timestamp epoch;
4537 int type,
4538 val;
4539 char *lowunits;
4540 fsec_t fsec;
4541 struct pg_tm tt,
4542 *tm = &tt;
4543
4544 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4545 VARSIZE_ANY_EXHDR(units),
4546 false);
4547
4548 type = DecodeUnits(0, lowunits, &val);
4549 if (type == UNKNOWN_FIELD)
4550 type = DecodeSpecial(0, lowunits, &val);
4551
4552 if (TIMESTAMP_NOT_FINITE(timestamp))
4553 {
4554 result = NonFiniteTimestampTzPart(type, val, lowunits,
4555 TIMESTAMP_IS_NOBEGIN(timestamp),
4556 false);
4557 if (result)
4558 PG_RETURN_FLOAT8(result);
4559 else
4560 PG_RETURN_NULL();
4561 }
4562
4563 if (type == UNITS)
4564 {
4565 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
4566 ereport(ERROR,
4567 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4568 errmsg("timestamp out of range")));
4569
4570 switch (val)
4571 {
4572 case DTK_MICROSEC:
4573 result = tm->tm_sec * 1000000.0 + fsec;
4574 break;
4575
4576 case DTK_MILLISEC:
4577 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
4578 break;
4579
4580 case DTK_SECOND:
4581 result = tm->tm_sec + fsec / 1000000.0;
4582 break;
4583
4584 case DTK_MINUTE:
4585 result = tm->tm_min;
4586 break;
4587
4588 case DTK_HOUR:
4589 result = tm->tm_hour;
4590 break;
4591
4592 case DTK_DAY:
4593 result = tm->tm_mday;
4594 break;
4595
4596 case DTK_MONTH:
4597 result = tm->tm_mon;
4598 break;
4599
4600 case DTK_QUARTER:
4601 result = (tm->tm_mon - 1) / 3 + 1;
4602 break;
4603
4604 case DTK_WEEK:
4605 result = (float8) date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
4606 break;
4607
4608 case DTK_YEAR:
4609 if (tm->tm_year > 0)
4610 result = tm->tm_year;
4611 else
4612 /* there is no year 0, just 1 BC and 1 AD */
4613 result = tm->tm_year - 1;
4614 break;
4615
4616 case DTK_DECADE:
4617
4618 /*
4619 * what is a decade wrt dates? let us assume that decade 199
4620 * is 1990 thru 1999... decade 0 starts on year 1 BC, and -1
4621 * is 11 BC thru 2 BC...
4622 */
4623 if (tm->tm_year >= 0)
4624 result = tm->tm_year / 10;
4625 else
4626 result = -((8 - (tm->tm_year - 1)) / 10);
4627 break;
4628
4629 case DTK_CENTURY:
4630
4631 /* ----
4632 * centuries AD, c>0: year in [ (c-1)* 100 + 1 : c*100 ]
4633 * centuries BC, c<0: year in [ c*100 : (c+1) * 100 - 1]
4634 * there is no number 0 century.
4635 * ----
4636 */
4637 if (tm->tm_year > 0)
4638 result = (tm->tm_year + 99) / 100;
4639 else
4640 /* caution: C division may have negative remainder */
4641 result = -((99 - (tm->tm_year - 1)) / 100);
4642 break;
4643
4644 case DTK_MILLENNIUM:
4645 /* see comments above. */
4646 if (tm->tm_year > 0)
4647 result = (tm->tm_year + 999) / 1000;
4648 else
4649 result = -((999 - (tm->tm_year - 1)) / 1000);
4650 break;
4651
4652 case DTK_JULIAN:
4653 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
4654 result += ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) +
4655 tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY;
4656 break;
4657
4658 case DTK_ISOYEAR:
4659 result = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
4660 /* Adjust BC years */
4661 if (result <= 0)
4662 result -= 1;
4663 break;
4664
4665 case DTK_DOW:
4666 case DTK_ISODOW:
4667 result = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
4668 if (val == DTK_ISODOW && result == 0)
4669 result = 7;
4670 break;
4671
4672 case DTK_DOY:
4673 result = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)
4674 - date2j(tm->tm_year, 1, 1) + 1);
4675 break;
4676
4677 case DTK_TZ:
4678 case DTK_TZ_MINUTE:
4679 case DTK_TZ_HOUR:
4680 default:
4681 ereport(ERROR,
4682 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4683 errmsg("timestamp units \"%s\" not supported",
4684 lowunits)));
4685 result = 0;
4686 }
4687 }
4688 else if (type == RESERV)
4689 {
4690 switch (val)
4691 {
4692 case DTK_EPOCH:
4693 epoch = SetEpochTimestamp();
4694 /* try to avoid precision loss in subtraction */
4695 if (timestamp < (PG_INT64_MAX + epoch))
4696 result = (timestamp - epoch) / 1000000.0;
4697 else
4698 result = ((float8) timestamp - epoch) / 1000000.0;
4699 break;
4700
4701 default:
4702 ereport(ERROR,
4703 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4704 errmsg("timestamp units \"%s\" not supported",
4705 lowunits)));
4706 result = 0;
4707 }
4708
4709 }
4710 else
4711 {
4712 ereport(ERROR,
4713 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4714 errmsg("timestamp units \"%s\" not recognized", lowunits)));
4715 result = 0;
4716 }
4717
4718 PG_RETURN_FLOAT8(result);
4719 }
4720
4721 /* timestamptz_part()
4722 * Extract specified field from timestamp with time zone.
4723 */
4724 Datum
timestamptz_part(PG_FUNCTION_ARGS)4725 timestamptz_part(PG_FUNCTION_ARGS)
4726 {
4727 text *units = PG_GETARG_TEXT_PP(0);
4728 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4729 float8 result;
4730 Timestamp epoch;
4731 int tz;
4732 int type,
4733 val;
4734 char *lowunits;
4735 double dummy;
4736 fsec_t fsec;
4737 struct pg_tm tt,
4738 *tm = &tt;
4739
4740 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4741 VARSIZE_ANY_EXHDR(units),
4742 false);
4743
4744 type = DecodeUnits(0, lowunits, &val);
4745 if (type == UNKNOWN_FIELD)
4746 type = DecodeSpecial(0, lowunits, &val);
4747
4748 if (TIMESTAMP_NOT_FINITE(timestamp))
4749 {
4750 result = NonFiniteTimestampTzPart(type, val, lowunits,
4751 TIMESTAMP_IS_NOBEGIN(timestamp),
4752 true);
4753 if (result)
4754 PG_RETURN_FLOAT8(result);
4755 else
4756 PG_RETURN_NULL();
4757 }
4758
4759 if (type == UNITS)
4760 {
4761 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
4762 ereport(ERROR,
4763 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4764 errmsg("timestamp out of range")));
4765
4766 switch (val)
4767 {
4768 case DTK_TZ:
4769 result = -tz;
4770 break;
4771
4772 case DTK_TZ_MINUTE:
4773 result = -tz;
4774 result /= MINS_PER_HOUR;
4775 FMODULO(result, dummy, (double) MINS_PER_HOUR);
4776 break;
4777
4778 case DTK_TZ_HOUR:
4779 dummy = -tz;
4780 FMODULO(dummy, result, (double) SECS_PER_HOUR);
4781 break;
4782
4783 case DTK_MICROSEC:
4784 result = tm->tm_sec * 1000000.0 + fsec;
4785 break;
4786
4787 case DTK_MILLISEC:
4788 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
4789 break;
4790
4791 case DTK_SECOND:
4792 result = tm->tm_sec + fsec / 1000000.0;
4793 break;
4794
4795 case DTK_MINUTE:
4796 result = tm->tm_min;
4797 break;
4798
4799 case DTK_HOUR:
4800 result = tm->tm_hour;
4801 break;
4802
4803 case DTK_DAY:
4804 result = tm->tm_mday;
4805 break;
4806
4807 case DTK_MONTH:
4808 result = tm->tm_mon;
4809 break;
4810
4811 case DTK_QUARTER:
4812 result = (tm->tm_mon - 1) / 3 + 1;
4813 break;
4814
4815 case DTK_WEEK:
4816 result = (float8) date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
4817 break;
4818
4819 case DTK_YEAR:
4820 if (tm->tm_year > 0)
4821 result = tm->tm_year;
4822 else
4823 /* there is no year 0, just 1 BC and 1 AD */
4824 result = tm->tm_year - 1;
4825 break;
4826
4827 case DTK_DECADE:
4828 /* see comments in timestamp_part */
4829 if (tm->tm_year > 0)
4830 result = tm->tm_year / 10;
4831 else
4832 result = -((8 - (tm->tm_year - 1)) / 10);
4833 break;
4834
4835 case DTK_CENTURY:
4836 /* see comments in timestamp_part */
4837 if (tm->tm_year > 0)
4838 result = (tm->tm_year + 99) / 100;
4839 else
4840 result = -((99 - (tm->tm_year - 1)) / 100);
4841 break;
4842
4843 case DTK_MILLENNIUM:
4844 /* see comments in timestamp_part */
4845 if (tm->tm_year > 0)
4846 result = (tm->tm_year + 999) / 1000;
4847 else
4848 result = -((999 - (tm->tm_year - 1)) / 1000);
4849 break;
4850
4851 case DTK_JULIAN:
4852 result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
4853 result += ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) +
4854 tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY;
4855 break;
4856
4857 case DTK_ISOYEAR:
4858 result = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
4859 /* Adjust BC years */
4860 if (result <= 0)
4861 result -= 1;
4862 break;
4863
4864 case DTK_DOW:
4865 case DTK_ISODOW:
4866 result = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
4867 if (val == DTK_ISODOW && result == 0)
4868 result = 7;
4869 break;
4870
4871 case DTK_DOY:
4872 result = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)
4873 - date2j(tm->tm_year, 1, 1) + 1);
4874 break;
4875
4876 default:
4877 ereport(ERROR,
4878 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4879 errmsg("timestamp with time zone units \"%s\" not supported",
4880 lowunits)));
4881 result = 0;
4882 }
4883
4884 }
4885 else if (type == RESERV)
4886 {
4887 switch (val)
4888 {
4889 case DTK_EPOCH:
4890 epoch = SetEpochTimestamp();
4891 /* try to avoid precision loss in subtraction */
4892 if (timestamp < (PG_INT64_MAX + epoch))
4893 result = (timestamp - epoch) / 1000000.0;
4894 else
4895 result = ((float8) timestamp - epoch) / 1000000.0;
4896 break;
4897
4898 default:
4899 ereport(ERROR,
4900 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4901 errmsg("timestamp with time zone units \"%s\" not supported",
4902 lowunits)));
4903 result = 0;
4904 }
4905 }
4906 else
4907 {
4908 ereport(ERROR,
4909 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4910 errmsg("timestamp with time zone units \"%s\" not recognized",
4911 lowunits)));
4912
4913 result = 0;
4914 }
4915
4916 PG_RETURN_FLOAT8(result);
4917 }
4918
4919
4920 /* interval_part()
4921 * Extract specified field from interval.
4922 */
4923 Datum
interval_part(PG_FUNCTION_ARGS)4924 interval_part(PG_FUNCTION_ARGS)
4925 {
4926 text *units = PG_GETARG_TEXT_PP(0);
4927 Interval *interval = PG_GETARG_INTERVAL_P(1);
4928 float8 result;
4929 int type,
4930 val;
4931 char *lowunits;
4932 fsec_t fsec;
4933 struct pg_tm tt,
4934 *tm = &tt;
4935
4936 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4937 VARSIZE_ANY_EXHDR(units),
4938 false);
4939
4940 type = DecodeUnits(0, lowunits, &val);
4941 if (type == UNKNOWN_FIELD)
4942 type = DecodeSpecial(0, lowunits, &val);
4943
4944 if (type == UNITS)
4945 {
4946 if (interval2tm(*interval, tm, &fsec) == 0)
4947 {
4948 switch (val)
4949 {
4950 case DTK_MICROSEC:
4951 result = tm->tm_sec * 1000000.0 + fsec;
4952 break;
4953
4954 case DTK_MILLISEC:
4955 result = tm->tm_sec * 1000.0 + fsec / 1000.0;
4956 break;
4957
4958 case DTK_SECOND:
4959 result = tm->tm_sec + fsec / 1000000.0;
4960 break;
4961
4962 case DTK_MINUTE:
4963 result = tm->tm_min;
4964 break;
4965
4966 case DTK_HOUR:
4967 result = tm->tm_hour;
4968 break;
4969
4970 case DTK_DAY:
4971 result = tm->tm_mday;
4972 break;
4973
4974 case DTK_MONTH:
4975 result = tm->tm_mon;
4976 break;
4977
4978 case DTK_QUARTER:
4979 result = (tm->tm_mon / 3) + 1;
4980 break;
4981
4982 case DTK_YEAR:
4983 result = tm->tm_year;
4984 break;
4985
4986 case DTK_DECADE:
4987 /* caution: C division may have negative remainder */
4988 result = tm->tm_year / 10;
4989 break;
4990
4991 case DTK_CENTURY:
4992 /* caution: C division may have negative remainder */
4993 result = tm->tm_year / 100;
4994 break;
4995
4996 case DTK_MILLENNIUM:
4997 /* caution: C division may have negative remainder */
4998 result = tm->tm_year / 1000;
4999 break;
5000
5001 default:
5002 ereport(ERROR,
5003 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5004 errmsg("interval units \"%s\" not supported",
5005 lowunits)));
5006 result = 0;
5007 }
5008
5009 }
5010 else
5011 {
5012 elog(ERROR, "could not convert interval to tm");
5013 result = 0;
5014 }
5015 }
5016 else if (type == RESERV && val == DTK_EPOCH)
5017 {
5018 result = interval->time / 1000000.0;
5019 result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
5020 result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
5021 result += ((double) SECS_PER_DAY) * interval->day;
5022 }
5023 else
5024 {
5025 ereport(ERROR,
5026 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5027 errmsg("interval units \"%s\" not recognized",
5028 lowunits)));
5029 result = 0;
5030 }
5031
5032 PG_RETURN_FLOAT8(result);
5033 }
5034
5035
5036 /* timestamp_zone()
5037 * Encode timestamp type with specified time zone.
5038 * This function is just timestamp2timestamptz() except instead of
5039 * shifting to the global timezone, we shift to the specified timezone.
5040 * This is different from the other AT TIME ZONE cases because instead
5041 * of shifting _to_ a new time zone, it sets the time to _be_ the
5042 * specified timezone.
5043 */
5044 Datum
timestamp_zone(PG_FUNCTION_ARGS)5045 timestamp_zone(PG_FUNCTION_ARGS)
5046 {
5047 text *zone = PG_GETARG_TEXT_PP(0);
5048 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
5049 TimestampTz result;
5050 int tz;
5051 char tzname[TZ_STRLEN_MAX + 1];
5052 char *lowzone;
5053 int type,
5054 val;
5055 pg_tz *tzp;
5056 struct pg_tm tm;
5057 fsec_t fsec;
5058
5059 if (TIMESTAMP_NOT_FINITE(timestamp))
5060 PG_RETURN_TIMESTAMPTZ(timestamp);
5061
5062 /*
5063 * Look up the requested timezone. First we look in the timezone
5064 * abbreviation table (to handle cases like "EST"), and if that fails, we
5065 * look in the timezone database (to handle cases like
5066 * "America/New_York"). (This matches the order in which timestamp input
5067 * checks the cases; it's important because the timezone database unwisely
5068 * uses a few zone names that are identical to offset abbreviations.)
5069 */
5070 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
5071
5072 /* DecodeTimezoneAbbrev requires lowercase input */
5073 lowzone = downcase_truncate_identifier(tzname,
5074 strlen(tzname),
5075 false);
5076
5077 type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
5078
5079 if (type == TZ || type == DTZ)
5080 {
5081 /* fixed-offset abbreviation */
5082 tz = val;
5083 result = dt2local(timestamp, tz);
5084 }
5085 else if (type == DYNTZ)
5086 {
5087 /* dynamic-offset abbreviation, resolve using specified time */
5088 if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
5089 ereport(ERROR,
5090 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5091 errmsg("timestamp out of range")));
5092 tz = -DetermineTimeZoneAbbrevOffset(&tm, tzname, tzp);
5093 result = dt2local(timestamp, tz);
5094 }
5095 else
5096 {
5097 /* try it as a full zone name */
5098 tzp = pg_tzset(tzname);
5099 if (tzp)
5100 {
5101 /* Apply the timezone change */
5102 if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
5103 ereport(ERROR,
5104 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5105 errmsg("timestamp out of range")));
5106 tz = DetermineTimeZoneOffset(&tm, tzp);
5107 if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
5108 ereport(ERROR,
5109 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5110 errmsg("timestamp out of range")));
5111 }
5112 else
5113 {
5114 ereport(ERROR,
5115 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5116 errmsg("time zone \"%s\" not recognized", tzname)));
5117 result = 0; /* keep compiler quiet */
5118 }
5119 }
5120
5121 if (!IS_VALID_TIMESTAMP(result))
5122 ereport(ERROR,
5123 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5124 errmsg("timestamp out of range")));
5125
5126 PG_RETURN_TIMESTAMPTZ(result);
5127 }
5128
5129 /* timestamp_izone()
5130 * Encode timestamp type with specified time interval as time zone.
5131 */
5132 Datum
timestamp_izone(PG_FUNCTION_ARGS)5133 timestamp_izone(PG_FUNCTION_ARGS)
5134 {
5135 Interval *zone = PG_GETARG_INTERVAL_P(0);
5136 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
5137 TimestampTz result;
5138 int tz;
5139
5140 if (TIMESTAMP_NOT_FINITE(timestamp))
5141 PG_RETURN_TIMESTAMPTZ(timestamp);
5142
5143 if (zone->month != 0 || zone->day != 0)
5144 ereport(ERROR,
5145 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5146 errmsg("interval time zone \"%s\" must not include months or days",
5147 DatumGetCString(DirectFunctionCall1(interval_out,
5148 PointerGetDatum(zone))))));
5149
5150 tz = zone->time / USECS_PER_SEC;
5151
5152 result = dt2local(timestamp, tz);
5153
5154 if (!IS_VALID_TIMESTAMP(result))
5155 ereport(ERROR,
5156 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5157 errmsg("timestamp out of range")));
5158
5159 PG_RETURN_TIMESTAMPTZ(result);
5160 } /* timestamp_izone() */
5161
5162 /* TimestampTimestampTzRequiresRewrite()
5163 *
5164 * Returns false if the TimeZone GUC setting causes timestamp_timestamptz and
5165 * timestamptz_timestamp to be no-ops, where the return value has the same
5166 * bits as the argument. Since project convention is to assume a GUC changes
5167 * no more often than STABLE functions change, the answer is valid that long.
5168 */
5169 bool
TimestampTimestampTzRequiresRewrite(void)5170 TimestampTimestampTzRequiresRewrite(void)
5171 {
5172 long offset;
5173
5174 if (pg_get_timezone_offset(session_timezone, &offset) && offset == 0)
5175 return false;
5176 return true;
5177 }
5178
5179 /* timestamp_timestamptz()
5180 * Convert local timestamp to timestamp at GMT
5181 */
5182 Datum
timestamp_timestamptz(PG_FUNCTION_ARGS)5183 timestamp_timestamptz(PG_FUNCTION_ARGS)
5184 {
5185 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
5186
5187 PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(timestamp));
5188 }
5189
5190 static TimestampTz
timestamp2timestamptz(Timestamp timestamp)5191 timestamp2timestamptz(Timestamp timestamp)
5192 {
5193 TimestampTz result;
5194 struct pg_tm tt,
5195 *tm = &tt;
5196 fsec_t fsec;
5197 int tz;
5198
5199 if (TIMESTAMP_NOT_FINITE(timestamp))
5200 result = timestamp;
5201 else
5202 {
5203 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
5204 ereport(ERROR,
5205 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5206 errmsg("timestamp out of range")));
5207
5208 tz = DetermineTimeZoneOffset(tm, session_timezone);
5209
5210 if (tm2timestamp(tm, fsec, &tz, &result) != 0)
5211 ereport(ERROR,
5212 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5213 errmsg("timestamp out of range")));
5214 }
5215
5216 return result;
5217 }
5218
5219 /* timestamptz_timestamp()
5220 * Convert timestamp at GMT to local timestamp
5221 */
5222 Datum
timestamptz_timestamp(PG_FUNCTION_ARGS)5223 timestamptz_timestamp(PG_FUNCTION_ARGS)
5224 {
5225 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
5226
5227 PG_RETURN_TIMESTAMP(timestamptz2timestamp(timestamp));
5228 }
5229
5230 static Timestamp
timestamptz2timestamp(TimestampTz timestamp)5231 timestamptz2timestamp(TimestampTz timestamp)
5232 {
5233 Timestamp result;
5234 struct pg_tm tt,
5235 *tm = &tt;
5236 fsec_t fsec;
5237 int tz;
5238
5239 if (TIMESTAMP_NOT_FINITE(timestamp))
5240 result = timestamp;
5241 else
5242 {
5243 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
5244 ereport(ERROR,
5245 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5246 errmsg("timestamp out of range")));
5247 if (tm2timestamp(tm, fsec, NULL, &result) != 0)
5248 ereport(ERROR,
5249 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5250 errmsg("timestamp out of range")));
5251 }
5252 return result;
5253 }
5254
5255 /* timestamptz_zone()
5256 * Evaluate timestamp with time zone type at the specified time zone.
5257 * Returns a timestamp without time zone.
5258 */
5259 Datum
timestamptz_zone(PG_FUNCTION_ARGS)5260 timestamptz_zone(PG_FUNCTION_ARGS)
5261 {
5262 text *zone = PG_GETARG_TEXT_PP(0);
5263 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
5264 Timestamp result;
5265 int tz;
5266 char tzname[TZ_STRLEN_MAX + 1];
5267 char *lowzone;
5268 int type,
5269 val;
5270 pg_tz *tzp;
5271
5272 if (TIMESTAMP_NOT_FINITE(timestamp))
5273 PG_RETURN_TIMESTAMP(timestamp);
5274
5275 /*
5276 * Look up the requested timezone. First we look in the timezone
5277 * abbreviation table (to handle cases like "EST"), and if that fails, we
5278 * look in the timezone database (to handle cases like
5279 * "America/New_York"). (This matches the order in which timestamp input
5280 * checks the cases; it's important because the timezone database unwisely
5281 * uses a few zone names that are identical to offset abbreviations.)
5282 */
5283 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
5284
5285 /* DecodeTimezoneAbbrev requires lowercase input */
5286 lowzone = downcase_truncate_identifier(tzname,
5287 strlen(tzname),
5288 false);
5289
5290 type = DecodeTimezoneAbbrev(0, lowzone, &val, &tzp);
5291
5292 if (type == TZ || type == DTZ)
5293 {
5294 /* fixed-offset abbreviation */
5295 tz = -val;
5296 result = dt2local(timestamp, tz);
5297 }
5298 else if (type == DYNTZ)
5299 {
5300 /* dynamic-offset abbreviation, resolve using specified time */
5301 int isdst;
5302
5303 tz = DetermineTimeZoneAbbrevOffsetTS(timestamp, tzname, tzp, &isdst);
5304 result = dt2local(timestamp, tz);
5305 }
5306 else
5307 {
5308 /* try it as a full zone name */
5309 tzp = pg_tzset(tzname);
5310 if (tzp)
5311 {
5312 /* Apply the timezone change */
5313 struct pg_tm tm;
5314 fsec_t fsec;
5315
5316 if (timestamp2tm(timestamp, &tz, &tm, &fsec, NULL, tzp) != 0)
5317 ereport(ERROR,
5318 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5319 errmsg("timestamp out of range")));
5320 if (tm2timestamp(&tm, fsec, NULL, &result) != 0)
5321 ereport(ERROR,
5322 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5323 errmsg("timestamp out of range")));
5324 }
5325 else
5326 {
5327 ereport(ERROR,
5328 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5329 errmsg("time zone \"%s\" not recognized", tzname)));
5330 result = 0; /* keep compiler quiet */
5331 }
5332 }
5333
5334 if (!IS_VALID_TIMESTAMP(result))
5335 ereport(ERROR,
5336 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5337 errmsg("timestamp out of range")));
5338
5339 PG_RETURN_TIMESTAMP(result);
5340 }
5341
5342 /* timestamptz_izone()
5343 * Encode timestamp with time zone type with specified time interval as time zone.
5344 * Returns a timestamp without time zone.
5345 */
5346 Datum
timestamptz_izone(PG_FUNCTION_ARGS)5347 timestamptz_izone(PG_FUNCTION_ARGS)
5348 {
5349 Interval *zone = PG_GETARG_INTERVAL_P(0);
5350 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
5351 Timestamp result;
5352 int tz;
5353
5354 if (TIMESTAMP_NOT_FINITE(timestamp))
5355 PG_RETURN_TIMESTAMP(timestamp);
5356
5357 if (zone->month != 0 || zone->day != 0)
5358 ereport(ERROR,
5359 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5360 errmsg("interval time zone \"%s\" must not include months or days",
5361 DatumGetCString(DirectFunctionCall1(interval_out,
5362 PointerGetDatum(zone))))));
5363
5364 tz = -(zone->time / USECS_PER_SEC);
5365
5366 result = dt2local(timestamp, tz);
5367
5368 if (!IS_VALID_TIMESTAMP(result))
5369 ereport(ERROR,
5370 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5371 errmsg("timestamp out of range")));
5372
5373 PG_RETURN_TIMESTAMP(result);
5374 }
5375
5376 /* generate_series_timestamp()
5377 * Generate the set of timestamps from start to finish by step
5378 */
5379 Datum
generate_series_timestamp(PG_FUNCTION_ARGS)5380 generate_series_timestamp(PG_FUNCTION_ARGS)
5381 {
5382 FuncCallContext *funcctx;
5383 generate_series_timestamp_fctx *fctx;
5384 Timestamp result;
5385
5386 /* stuff done only on the first call of the function */
5387 if (SRF_IS_FIRSTCALL())
5388 {
5389 Timestamp start = PG_GETARG_TIMESTAMP(0);
5390 Timestamp finish = PG_GETARG_TIMESTAMP(1);
5391 Interval *step = PG_GETARG_INTERVAL_P(2);
5392 MemoryContext oldcontext;
5393 Interval interval_zero;
5394
5395 /* create a function context for cross-call persistence */
5396 funcctx = SRF_FIRSTCALL_INIT();
5397
5398 /*
5399 * switch to memory context appropriate for multiple function calls
5400 */
5401 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
5402
5403 /* allocate memory for user context */
5404 fctx = (generate_series_timestamp_fctx *)
5405 palloc(sizeof(generate_series_timestamp_fctx));
5406
5407 /*
5408 * Use fctx to keep state from call to call. Seed current with the
5409 * original start value
5410 */
5411 fctx->current = start;
5412 fctx->finish = finish;
5413 fctx->step = *step;
5414
5415 /* Determine sign of the interval */
5416 MemSet(&interval_zero, 0, sizeof(Interval));
5417 fctx->step_sign = interval_cmp_internal(&fctx->step, &interval_zero);
5418
5419 if (fctx->step_sign == 0)
5420 ereport(ERROR,
5421 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5422 errmsg("step size cannot equal zero")));
5423
5424 funcctx->user_fctx = fctx;
5425 MemoryContextSwitchTo(oldcontext);
5426 }
5427
5428 /* stuff done on every call of the function */
5429 funcctx = SRF_PERCALL_SETUP();
5430
5431 /*
5432 * get the saved state and use current as the result for this iteration
5433 */
5434 fctx = funcctx->user_fctx;
5435 result = fctx->current;
5436
5437 if (fctx->step_sign > 0 ?
5438 timestamp_cmp_internal(result, fctx->finish) <= 0 :
5439 timestamp_cmp_internal(result, fctx->finish) >= 0)
5440 {
5441 /* increment current in preparation for next iteration */
5442 fctx->current = DatumGetTimestamp(
5443 DirectFunctionCall2(timestamp_pl_interval,
5444 TimestampGetDatum(fctx->current),
5445 PointerGetDatum(&fctx->step)));
5446
5447 /* do when there is more left to send */
5448 SRF_RETURN_NEXT(funcctx, TimestampGetDatum(result));
5449 }
5450 else
5451 {
5452 /* do when there is no more left */
5453 SRF_RETURN_DONE(funcctx);
5454 }
5455 }
5456
5457 /* generate_series_timestamptz()
5458 * Generate the set of timestamps from start to finish by step
5459 */
5460 Datum
generate_series_timestamptz(PG_FUNCTION_ARGS)5461 generate_series_timestamptz(PG_FUNCTION_ARGS)
5462 {
5463 FuncCallContext *funcctx;
5464 generate_series_timestamptz_fctx *fctx;
5465 TimestampTz result;
5466
5467 /* stuff done only on the first call of the function */
5468 if (SRF_IS_FIRSTCALL())
5469 {
5470 TimestampTz start = PG_GETARG_TIMESTAMPTZ(0);
5471 TimestampTz finish = PG_GETARG_TIMESTAMPTZ(1);
5472 Interval *step = PG_GETARG_INTERVAL_P(2);
5473 MemoryContext oldcontext;
5474 Interval interval_zero;
5475
5476 /* create a function context for cross-call persistence */
5477 funcctx = SRF_FIRSTCALL_INIT();
5478
5479 /*
5480 * switch to memory context appropriate for multiple function calls
5481 */
5482 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
5483
5484 /* allocate memory for user context */
5485 fctx = (generate_series_timestamptz_fctx *)
5486 palloc(sizeof(generate_series_timestamptz_fctx));
5487
5488 /*
5489 * Use fctx to keep state from call to call. Seed current with the
5490 * original start value
5491 */
5492 fctx->current = start;
5493 fctx->finish = finish;
5494 fctx->step = *step;
5495
5496 /* Determine sign of the interval */
5497 MemSet(&interval_zero, 0, sizeof(Interval));
5498 fctx->step_sign = interval_cmp_internal(&fctx->step, &interval_zero);
5499
5500 if (fctx->step_sign == 0)
5501 ereport(ERROR,
5502 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5503 errmsg("step size cannot equal zero")));
5504
5505 funcctx->user_fctx = fctx;
5506 MemoryContextSwitchTo(oldcontext);
5507 }
5508
5509 /* stuff done on every call of the function */
5510 funcctx = SRF_PERCALL_SETUP();
5511
5512 /*
5513 * get the saved state and use current as the result for this iteration
5514 */
5515 fctx = funcctx->user_fctx;
5516 result = fctx->current;
5517
5518 if (fctx->step_sign > 0 ?
5519 timestamp_cmp_internal(result, fctx->finish) <= 0 :
5520 timestamp_cmp_internal(result, fctx->finish) >= 0)
5521 {
5522 /* increment current in preparation for next iteration */
5523 fctx->current = DatumGetTimestampTz(
5524 DirectFunctionCall2(timestamptz_pl_interval,
5525 TimestampTzGetDatum(fctx->current),
5526 PointerGetDatum(&fctx->step)));
5527
5528 /* do when there is more left to send */
5529 SRF_RETURN_NEXT(funcctx, TimestampTzGetDatum(result));
5530 }
5531 else
5532 {
5533 /* do when there is no more left */
5534 SRF_RETURN_DONE(funcctx);
5535 }
5536 }
5537