?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
sepgsql: update TAP test to use fat comma style
[pgsql.git] / src / backend / utils / adt / timestamp.c
blobba9bae05069a6f737d4167d2bf19aaff4c6f4f8b
1 /*-------------------------------------------------------------------------
2 *
3 * timestamp.c
4 * Functions for the built-in SQL types "timestamp" and "interval".
5 *
6 * Portions Copyright (c) 1996-2025, 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/int.h"
26 #include "common/int128.h"
27 #include "funcapi.h"
28 #include "libpq/pqformat.h"
29 #include "miscadmin.h"
30 #include "nodes/nodeFuncs.h"
31 #include "nodes/supportnodes.h"
32 #include "optimizer/optimizer.h"
33 #include "parser/scansup.h"
34 #include "utils/array.h"
35 #include "utils/builtins.h"
36 #include "utils/date.h"
37 #include "utils/datetime.h"
38 #include "utils/float.h"
39 #include "utils/numeric.h"
40 #include "utils/sortsupport.h"
41
42 /*
43 * gcc's -ffast-math switch breaks routines that expect exact results from
44 * expressions like timeval / SECS_PER_HOUR, where timeval is double.
45 */
46 #ifdef __FAST_MATH__
47 #error -ffast-math is known to break this code
48 #endif
49
50 #define SAMESIGN(a,b) (((a) < 0) == ((b) < 0))
51
52 /* Set at postmaster start */
53 TimestampTz PgStartTime;
54
55 /* Set at configuration reload */
56 TimestampTz PgReloadTime;
57
58 typedef struct
59 {
60 Timestamp current;
61 Timestamp finish;
62 Interval step;
63 int step_sign;
64 } generate_series_timestamp_fctx;
65
66 typedef struct
67 {
68 TimestampTz current;
69 TimestampTz finish;
70 Interval step;
71 int step_sign;
72 pg_tz *attimezone;
73 } generate_series_timestamptz_fctx;
74
75 /*
76 * The transition datatype for interval aggregates is declared as internal.
77 * It's a pointer to an IntervalAggState allocated in the aggregate context.
78 */
79 typedef struct IntervalAggState
80 {
81 int64 N; /* count of finite intervals processed */
82 Interval sumX; /* sum of finite intervals processed */
83 /* These counts are *not* included in N! Use IA_TOTAL_COUNT() as needed */
84 int64 pInfcount; /* count of +infinity intervals */
85 int64 nInfcount; /* count of -infinity intervals */
86 } IntervalAggState;
87
88 #define IA_TOTAL_COUNT(ia) \
89 ((ia)->N + (ia)->pInfcount + (ia)->nInfcount)
90
91 static TimeOffset time2t(const int hour, const int min, const int sec, const fsec_t fsec);
92 static Timestamp dt2local(Timestamp dt, int timezone);
93 static bool AdjustIntervalForTypmod(Interval *interval, int32 typmod,
94 Node *escontext);
95 static TimestampTz timestamp2timestamptz(Timestamp timestamp);
96 static Timestamp timestamptz2timestamp(TimestampTz timestamp);
97
98 static void EncodeSpecialInterval(const Interval *interval, char *str);
99 static void interval_um_internal(const Interval *interval, Interval *result);
100
101 /* common code for timestamptypmodin and timestamptztypmodin */
102 static int32
103 anytimestamp_typmodin(bool istz, ArrayType *ta)
104 {
105 int32 *tl;
106 int n;
107
108 tl = ArrayGetIntegerTypmods(ta, &n);
109
110 /*
111 * we're not too tense about good error message here because grammar
112 * shouldn't allow wrong number of modifiers for TIMESTAMP
113 */
114 if (n != 1)
115 ereport(ERROR,
116 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
117 errmsg("invalid type modifier")));
118
119 return anytimestamp_typmod_check(istz, tl[0]);
120 }
121
122 /* exported so parse_expr.c can use it */
123 int32
124 anytimestamp_typmod_check(bool istz, int32 typmod)
125 {
126 if (typmod < 0)
127 ereport(ERROR,
128 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
129 errmsg("TIMESTAMP(%d)%s precision must not be negative",
130 typmod, (istz ? " WITH TIME ZONE" : ""))));
131 if (typmod > MAX_TIMESTAMP_PRECISION)
132 {
133 ereport(WARNING,
134 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
135 errmsg("TIMESTAMP(%d)%s precision reduced to maximum allowed, %d",
136 typmod, (istz ? " WITH TIME ZONE" : ""),
137 MAX_TIMESTAMP_PRECISION)));
138 typmod = MAX_TIMESTAMP_PRECISION;
139 }
140
141 return typmod;
142 }
143
144 /* common code for timestamptypmodout and timestamptztypmodout */
145 static char *
146 anytimestamp_typmodout(bool istz, int32 typmod)
147 {
148 const char *tz = istz ? " with time zone" : " without time zone";
149
150 if (typmod >= 0)
151 return psprintf("(%d)%s", (int) typmod, tz);
152 else
153 return pstrdup(tz);
154 }
155
156
157 /*****************************************************************************
158 * USER I/O ROUTINES *
159 *****************************************************************************/
160
161 /* timestamp_in()
162 * Convert a string to internal form.
163 */
164 Datum
165 timestamp_in(PG_FUNCTION_ARGS)
166 {
167 char *str = PG_GETARG_CSTRING(0);
168 #ifdef NOT_USED
169 Oid typelem = PG_GETARG_OID(1);
170 #endif
171 int32 typmod = PG_GETARG_INT32(2);
172 Node *escontext = fcinfo->context;
173 Timestamp result;
174 fsec_t fsec;
175 struct pg_tm tt,
176 *tm = &tt;
177 int tz;
178 int dtype;
179 int nf;
180 int dterr;
181 char *field[MAXDATEFIELDS];
182 int ftype[MAXDATEFIELDS];
183 char workbuf[MAXDATELEN + MAXDATEFIELDS];
184 DateTimeErrorExtra extra;
185
186 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
187 field, ftype, MAXDATEFIELDS, &nf);
188 if (dterr == 0)
189 dterr = DecodeDateTime(field, ftype, nf,
190 &dtype, tm, &fsec, &tz, &extra);
191 if (dterr != 0)
192 {
193 DateTimeParseError(dterr, &extra, str, "timestamp", escontext);
194 PG_RETURN_NULL();
195 }
196
197 switch (dtype)
198 {
199 case DTK_DATE:
200 if (tm2timestamp(tm, fsec, NULL, &result) != 0)
201 ereturn(escontext, (Datum) 0,
202 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
203 errmsg("timestamp out of range: \"%s\"", str)));
204 break;
205
206 case DTK_EPOCH:
207 result = SetEpochTimestamp();
208 break;
209
210 case DTK_LATE:
211 TIMESTAMP_NOEND(result);
212 break;
213
214 case DTK_EARLY:
215 TIMESTAMP_NOBEGIN(result);
216 break;
217
218 default:
219 elog(ERROR, "unexpected dtype %d while parsing timestamp \"%s\"",
220 dtype, str);
221 TIMESTAMP_NOEND(result);
222 }
223
224 AdjustTimestampForTypmod(&result, typmod, escontext);
225
226 PG_RETURN_TIMESTAMP(result);
227 }
228
229 /* timestamp_out()
230 * Convert a timestamp to external form.
231 */
232 Datum
233 timestamp_out(PG_FUNCTION_ARGS)
234 {
235 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
236 char *result;
237 struct pg_tm tt,
238 *tm = &tt;
239 fsec_t fsec;
240 char buf[MAXDATELEN + 1];
241
242 if (TIMESTAMP_NOT_FINITE(timestamp))
243 EncodeSpecialTimestamp(timestamp, buf);
244 else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
245 EncodeDateTime(tm, fsec, false, 0, NULL, DateStyle, buf);
246 else
247 ereport(ERROR,
248 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
249 errmsg("timestamp out of range")));
250
251 result = pstrdup(buf);
252 PG_RETURN_CSTRING(result);
253 }
254
255 /*
256 * timestamp_recv - converts external binary format to timestamp
257 */
258 Datum
259 timestamp_recv(PG_FUNCTION_ARGS)
260 {
261 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
262
263 #ifdef NOT_USED
264 Oid typelem = PG_GETARG_OID(1);
265 #endif
266 int32 typmod = PG_GETARG_INT32(2);
267 Timestamp timestamp;
268 struct pg_tm tt,
269 *tm = &tt;
270 fsec_t fsec;
271
272 timestamp = (Timestamp) pq_getmsgint64(buf);
273
274 /* range check: see if timestamp_out would like it */
275 if (TIMESTAMP_NOT_FINITE(timestamp))
276 /* ok */ ;
277 else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0 ||
278 !IS_VALID_TIMESTAMP(timestamp))
279 ereport(ERROR,
280 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
281 errmsg("timestamp out of range")));
282
283 AdjustTimestampForTypmod(&timestamp, typmod, NULL);
284
285 PG_RETURN_TIMESTAMP(timestamp);
286 }
287
288 /*
289 * timestamp_send - converts timestamp to binary format
290 */
291 Datum
292 timestamp_send(PG_FUNCTION_ARGS)
293 {
294 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
295 StringInfoData buf;
296
297 pq_begintypsend(&buf);
298 pq_sendint64(&buf, timestamp);
299 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
300 }
301
302 Datum
303 timestamptypmodin(PG_FUNCTION_ARGS)
304 {
305 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
306
307 PG_RETURN_INT32(anytimestamp_typmodin(false, ta));
308 }
309
310 Datum
311 timestamptypmodout(PG_FUNCTION_ARGS)
312 {
313 int32 typmod = PG_GETARG_INT32(0);
314
315 PG_RETURN_CSTRING(anytimestamp_typmodout(false, typmod));
316 }
317
318
319 /*
320 * timestamp_support()
321 *
322 * Planner support function for the timestamp_scale() and timestamptz_scale()
323 * length coercion functions (we need not distinguish them here).
324 */
325 Datum
326 timestamp_support(PG_FUNCTION_ARGS)
327 {
328 Node *rawreq = (Node *) PG_GETARG_POINTER(0);
329 Node *ret = NULL;
330
331 if (IsA(rawreq, SupportRequestSimplify))
332 {
333 SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
334
335 ret = TemporalSimplify(MAX_TIMESTAMP_PRECISION, (Node *) req->fcall);
336 }
337
338 PG_RETURN_POINTER(ret);
339 }
340
341 /* timestamp_scale()
342 * Adjust time type for specified scale factor.
343 * Used by PostgreSQL type system to stuff columns.
344 */
345 Datum
346 timestamp_scale(PG_FUNCTION_ARGS)
347 {
348 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
349 int32 typmod = PG_GETARG_INT32(1);
350 Timestamp result;
351
352 result = timestamp;
353
354 AdjustTimestampForTypmod(&result, typmod, NULL);
355
356 PG_RETURN_TIMESTAMP(result);
357 }
358
359 /*
360 * AdjustTimestampForTypmod --- round off a timestamp to suit given typmod
361 * Works for either timestamp or timestamptz.
362 *
363 * Returns true on success, false on failure (if escontext points to an
364 * ErrorSaveContext; otherwise errors are thrown).
365 */
366 bool
367 AdjustTimestampForTypmod(Timestamp *time, int32 typmod, Node *escontext)
368 {
369 static const int64 TimestampScales[MAX_TIMESTAMP_PRECISION + 1] = {
370 INT64CONST(1000000),
371 INT64CONST(100000),
372 INT64CONST(10000),
373 INT64CONST(1000),
374 INT64CONST(100),
375 INT64CONST(10),
376 INT64CONST(1)
377 };
378
379 static const int64 TimestampOffsets[MAX_TIMESTAMP_PRECISION + 1] = {
380 INT64CONST(500000),
381 INT64CONST(50000),
382 INT64CONST(5000),
383 INT64CONST(500),
384 INT64CONST(50),
385 INT64CONST(5),
386 INT64CONST(0)
387 };
388
389 if (!TIMESTAMP_NOT_FINITE(*time)
390 && (typmod != -1) && (typmod != MAX_TIMESTAMP_PRECISION))
391 {
392 if (typmod < 0 || typmod > MAX_TIMESTAMP_PRECISION)
393 ereturn(escontext, false,
394 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
395 errmsg("timestamp(%d) precision must be between %d and %d",
396 typmod, 0, MAX_TIMESTAMP_PRECISION)));
397
398 if (*time >= INT64CONST(0))
399 {
400 *time = ((*time + TimestampOffsets[typmod]) / TimestampScales[typmod]) *
401 TimestampScales[typmod];
402 }
403 else
404 {
405 *time = -((((-*time) + TimestampOffsets[typmod]) / TimestampScales[typmod])
406 * TimestampScales[typmod]);
407 }
408 }
409
410 return true;
411 }
412
413 /* timestamptz_in()
414 * Convert a string to internal form.
415 */
416 Datum
417 timestamptz_in(PG_FUNCTION_ARGS)
418 {
419 char *str = PG_GETARG_CSTRING(0);
420 #ifdef NOT_USED
421 Oid typelem = PG_GETARG_OID(1);
422 #endif
423 int32 typmod = PG_GETARG_INT32(2);
424 Node *escontext = fcinfo->context;
425 TimestampTz result;
426 fsec_t fsec;
427 struct pg_tm tt,
428 *tm = &tt;
429 int tz;
430 int dtype;
431 int nf;
432 int dterr;
433 char *field[MAXDATEFIELDS];
434 int ftype[MAXDATEFIELDS];
435 char workbuf[MAXDATELEN + MAXDATEFIELDS];
436 DateTimeErrorExtra extra;
437
438 dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
439 field, ftype, MAXDATEFIELDS, &nf);
440 if (dterr == 0)
441 dterr = DecodeDateTime(field, ftype, nf,
442 &dtype, tm, &fsec, &tz, &extra);
443 if (dterr != 0)
444 {
445 DateTimeParseError(dterr, &extra, str, "timestamp with time zone",
446 escontext);
447 PG_RETURN_NULL();
448 }
449
450 switch (dtype)
451 {
452 case DTK_DATE:
453 if (tm2timestamp(tm, fsec, &tz, &result) != 0)
454 ereturn(escontext, (Datum) 0,
455 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
456 errmsg("timestamp out of range: \"%s\"", str)));
457 break;
458
459 case DTK_EPOCH:
460 result = SetEpochTimestamp();
461 break;
462
463 case DTK_LATE:
464 TIMESTAMP_NOEND(result);
465 break;
466
467 case DTK_EARLY:
468 TIMESTAMP_NOBEGIN(result);
469 break;
470
471 default:
472 elog(ERROR, "unexpected dtype %d while parsing timestamptz \"%s\"",
473 dtype, str);
474 TIMESTAMP_NOEND(result);
475 }
476
477 AdjustTimestampForTypmod(&result, typmod, escontext);
478
479 PG_RETURN_TIMESTAMPTZ(result);
480 }
481
482 /*
483 * Try to parse a timezone specification, and return its timezone offset value
484 * if it's acceptable. Otherwise, an error is thrown.
485 *
486 * Note: some code paths update tm->tm_isdst, and some don't; current callers
487 * don't care, so we don't bother being consistent.
488 */
489 static int
490 parse_sane_timezone(struct pg_tm *tm, text *zone)
491 {
492 char tzname[TZ_STRLEN_MAX + 1];
493 int dterr;
494 int tz;
495
496 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
497
498 /*
499 * Look up the requested timezone. First we try to interpret it as a
500 * numeric timezone specification; if DecodeTimezone decides it doesn't
501 * like the format, we try timezone abbreviations and names.
502 *
503 * Note pg_tzset happily parses numeric input that DecodeTimezone would
504 * reject. To avoid having it accept input that would otherwise be seen
505 * as invalid, it's enough to disallow having a digit in the first
506 * position of our input string.
507 */
508 if (isdigit((unsigned char) *tzname))
509 ereport(ERROR,
510 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
511 errmsg("invalid input syntax for type %s: \"%s\"",
512 "numeric time zone", tzname),
513 errhint("Numeric time zones must have \"-\" or \"+\" as first character.")));
514
515 dterr = DecodeTimezone(tzname, &tz);
516 if (dterr != 0)
517 {
518 int type,
519 val;
520 pg_tz *tzp;
521
522 if (dterr == DTERR_TZDISP_OVERFLOW)
523 ereport(ERROR,
524 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
525 errmsg("numeric time zone \"%s\" out of range", tzname)));
526 else if (dterr != DTERR_BAD_FORMAT)
527 ereport(ERROR,
528 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
529 errmsg("time zone \"%s\" not recognized", tzname)));
530
531 type = DecodeTimezoneName(tzname, &val, &tzp);
532
533 if (type == TZNAME_FIXED_OFFSET)
534 {
535 /* fixed-offset abbreviation */
536 tz = -val;
537 }
538 else if (type == TZNAME_DYNTZ)
539 {
540 /* dynamic-offset abbreviation, resolve using specified time */
541 tz = DetermineTimeZoneAbbrevOffset(tm, tzname, tzp);
542 }
543 else
544 {
545 /* full zone name */
546 tz = DetermineTimeZoneOffset(tm, tzp);
547 }
548 }
549
550 return tz;
551 }
552
553 /*
554 * Look up the requested timezone, returning a pg_tz struct.
555 *
556 * This is the same as DecodeTimezoneNameToTz, but starting with a text Datum.
557 */
558 static pg_tz *
559 lookup_timezone(text *zone)
560 {
561 char tzname[TZ_STRLEN_MAX + 1];
562
563 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
564
565 return DecodeTimezoneNameToTz(tzname);
566 }
567
568 /*
569 * make_timestamp_internal
570 * workhorse for make_timestamp and make_timestamptz
571 */
572 static Timestamp
573 make_timestamp_internal(int year, int month, int day,
574 int hour, int min, double sec)
575 {
576 struct pg_tm tm;
577 TimeOffset date;
578 TimeOffset time;
579 int dterr;
580 bool bc = false;
581 Timestamp result;
582
583 tm.tm_year = year;
584 tm.tm_mon = month;
585 tm.tm_mday = day;
586
587 /* Handle negative years as BC */
588 if (tm.tm_year < 0)
589 {
590 bc = true;
591 tm.tm_year = -tm.tm_year;
592 }
593
594 dterr = ValidateDate(DTK_DATE_M, false, false, bc, &tm);
595
596 if (dterr != 0)
597 ereport(ERROR,
598 (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
599 errmsg("date field value out of range: %d-%02d-%02d",
600 year, month, day)));
601
602 if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
603 ereport(ERROR,
604 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
605 errmsg("date out of range: %d-%02d-%02d",
606 year, month, day)));
607
608 date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
609
610 /* Check for time overflow */
611 if (float_time_overflows(hour, min, sec))
612 ereport(ERROR,
613 (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
614 errmsg("time field value out of range: %d:%02d:%02g",
615 hour, min, sec)));
616
617 /* This should match tm2time */
618 time = (((hour * MINS_PER_HOUR + min) * SECS_PER_MINUTE)
619 * USECS_PER_SEC) + (int64) rint(sec * USECS_PER_SEC);
620
621 if (unlikely(pg_mul_s64_overflow(date, USECS_PER_DAY, &result) ||
622 pg_add_s64_overflow(result, time, &result)))
623 ereport(ERROR,
624 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
625 errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
626 year, month, day,
627 hour, min, sec)));
628
629 /* final range check catches just-out-of-range timestamps */
630 if (!IS_VALID_TIMESTAMP(result))
631 ereport(ERROR,
632 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
633 errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
634 year, month, day,
635 hour, min, sec)));
636
637 return result;
638 }
639
640 /*
641 * make_timestamp() - timestamp constructor
642 */
643 Datum
644 make_timestamp(PG_FUNCTION_ARGS)
645 {
646 int32 year = PG_GETARG_INT32(0);
647 int32 month = PG_GETARG_INT32(1);
648 int32 mday = PG_GETARG_INT32(2);
649 int32 hour = PG_GETARG_INT32(3);
650 int32 min = PG_GETARG_INT32(4);
651 float8 sec = PG_GETARG_FLOAT8(5);
652 Timestamp result;
653
654 result = make_timestamp_internal(year, month, mday,
655 hour, min, sec);
656
657 PG_RETURN_TIMESTAMP(result);
658 }
659
660 /*
661 * make_timestamptz() - timestamp with time zone constructor
662 */
663 Datum
664 make_timestamptz(PG_FUNCTION_ARGS)
665 {
666 int32 year = PG_GETARG_INT32(0);
667 int32 month = PG_GETARG_INT32(1);
668 int32 mday = PG_GETARG_INT32(2);
669 int32 hour = PG_GETARG_INT32(3);
670 int32 min = PG_GETARG_INT32(4);
671 float8 sec = PG_GETARG_FLOAT8(5);
672 Timestamp result;
673
674 result = make_timestamp_internal(year, month, mday,
675 hour, min, sec);
676
677 PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(result));
678 }
679
680 /*
681 * Construct a timestamp with time zone.
682 * As above, but the time zone is specified as seventh argument.
683 */
684 Datum
685 make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
686 {
687 int32 year = PG_GETARG_INT32(0);
688 int32 month = PG_GETARG_INT32(1);
689 int32 mday = PG_GETARG_INT32(2);
690 int32 hour = PG_GETARG_INT32(3);
691 int32 min = PG_GETARG_INT32(4);
692 float8 sec = PG_GETARG_FLOAT8(5);
693 text *zone = PG_GETARG_TEXT_PP(6);
694 TimestampTz result;
695 Timestamp timestamp;
696 struct pg_tm tt;
697 int tz;
698 fsec_t fsec;
699
700 timestamp = make_timestamp_internal(year, month, mday,
701 hour, min, sec);
702
703 if (timestamp2tm(timestamp, NULL, &tt, &fsec, NULL, NULL) != 0)
704 ereport(ERROR,
705 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
706 errmsg("timestamp out of range")));
707
708 tz = parse_sane_timezone(&tt, zone);
709
710 result = dt2local(timestamp, -tz);
711
712 if (!IS_VALID_TIMESTAMP(result))
713 ereport(ERROR,
714 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
715 errmsg("timestamp out of range")));
716
717 PG_RETURN_TIMESTAMPTZ(result);
718 }
719
720 /*
721 * to_timestamp(double precision)
722 * Convert UNIX epoch to timestamptz.
723 */
724 Datum
725 float8_timestamptz(PG_FUNCTION_ARGS)
726 {
727 float8 seconds = PG_GETARG_FLOAT8(0);
728 TimestampTz result;
729
730 /* Deal with NaN and infinite inputs ... */
731 if (isnan(seconds))
732 ereport(ERROR,
733 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
734 errmsg("timestamp cannot be NaN")));
735
736 if (isinf(seconds))
737 {
738 if (seconds < 0)
739 TIMESTAMP_NOBEGIN(result);
740 else
741 TIMESTAMP_NOEND(result);
742 }
743 else
744 {
745 /* Out of range? */
746 if (seconds <
747 (float8) SECS_PER_DAY * (DATETIME_MIN_JULIAN - UNIX_EPOCH_JDATE)
748 || seconds >=
749 (float8) SECS_PER_DAY * (TIMESTAMP_END_JULIAN - UNIX_EPOCH_JDATE))
750 ereport(ERROR,
751 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
752 errmsg("timestamp out of range: \"%g\"", seconds)));
753
754 /* Convert UNIX epoch to Postgres epoch */
755 seconds -= ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
756
757 seconds = rint(seconds * USECS_PER_SEC);
758 result = (int64) seconds;
759
760 /* Recheck in case roundoff produces something just out of range */
761 if (!IS_VALID_TIMESTAMP(result))
762 ereport(ERROR,
763 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
764 errmsg("timestamp out of range: \"%g\"",
765 PG_GETARG_FLOAT8(0))));
766 }
767
768 PG_RETURN_TIMESTAMP(result);
769 }
770
771 /* timestamptz_out()
772 * Convert a timestamp to external form.
773 */
774 Datum
775 timestamptz_out(PG_FUNCTION_ARGS)
776 {
777 TimestampTz dt = PG_GETARG_TIMESTAMPTZ(0);
778 char *result;
779 int tz;
780 struct pg_tm tt,
781 *tm = &tt;
782 fsec_t fsec;
783 const char *tzn;
784 char buf[MAXDATELEN + 1];
785
786 if (TIMESTAMP_NOT_FINITE(dt))
787 EncodeSpecialTimestamp(dt, buf);
788 else if (timestamp2tm(dt, &tz, tm, &fsec, &tzn, NULL) == 0)
789 EncodeDateTime(tm, fsec, true, tz, tzn, DateStyle, buf);
790 else
791 ereport(ERROR,
792 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
793 errmsg("timestamp out of range")));
794
795 result = pstrdup(buf);
796 PG_RETURN_CSTRING(result);
797 }
798
799 /*
800 * timestamptz_recv - converts external binary format to timestamptz
801 */
802 Datum
803 timestamptz_recv(PG_FUNCTION_ARGS)
804 {
805 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
806
807 #ifdef NOT_USED
808 Oid typelem = PG_GETARG_OID(1);
809 #endif
810 int32 typmod = PG_GETARG_INT32(2);
811 TimestampTz timestamp;
812 int tz;
813 struct pg_tm tt,
814 *tm = &tt;
815 fsec_t fsec;
816
817 timestamp = (TimestampTz) pq_getmsgint64(buf);
818
819 /* range check: see if timestamptz_out would like it */
820 if (TIMESTAMP_NOT_FINITE(timestamp))
821 /* ok */ ;
822 else if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0 ||
823 !IS_VALID_TIMESTAMP(timestamp))
824 ereport(ERROR,
825 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
826 errmsg("timestamp out of range")));
827
828 AdjustTimestampForTypmod(&timestamp, typmod, NULL);
829
830 PG_RETURN_TIMESTAMPTZ(timestamp);
831 }
832
833 /*
834 * timestamptz_send - converts timestamptz to binary format
835 */
836 Datum
837 timestamptz_send(PG_FUNCTION_ARGS)
838 {
839 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
840 StringInfoData buf;
841
842 pq_begintypsend(&buf);
843 pq_sendint64(&buf, timestamp);
844 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
845 }
846
847 Datum
848 timestamptztypmodin(PG_FUNCTION_ARGS)
849 {
850 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
851
852 PG_RETURN_INT32(anytimestamp_typmodin(true, ta));
853 }
854
855 Datum
856 timestamptztypmodout(PG_FUNCTION_ARGS)
857 {
858 int32 typmod = PG_GETARG_INT32(0);
859
860 PG_RETURN_CSTRING(anytimestamp_typmodout(true, typmod));
861 }
862
863
864 /* timestamptz_scale()
865 * Adjust time type for specified scale factor.
866 * Used by PostgreSQL type system to stuff columns.
867 */
868 Datum
869 timestamptz_scale(PG_FUNCTION_ARGS)
870 {
871 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
872 int32 typmod = PG_GETARG_INT32(1);
873 TimestampTz result;
874
875 result = timestamp;
876
877 AdjustTimestampForTypmod(&result, typmod, NULL);
878
879 PG_RETURN_TIMESTAMPTZ(result);
880 }
881
882
883 /* interval_in()
884 * Convert a string to internal form.
885 *
886 * External format(s):
887 * Uses the generic date/time parsing and decoding routines.
888 */
889 Datum
890 interval_in(PG_FUNCTION_ARGS)
891 {
892 char *str = PG_GETARG_CSTRING(0);
893 #ifdef NOT_USED
894 Oid typelem = PG_GETARG_OID(1);
895 #endif
896 int32 typmod = PG_GETARG_INT32(2);
897 Node *escontext = fcinfo->context;
898 Interval *result;
899 struct pg_itm_in tt,
900 *itm_in = &tt;
901 int dtype;
902 int nf;
903 int range;
904 int dterr;
905 char *field[MAXDATEFIELDS];
906 int ftype[MAXDATEFIELDS];
907 char workbuf[256];
908 DateTimeErrorExtra extra;
909
910 itm_in->tm_year = 0;
911 itm_in->tm_mon = 0;
912 itm_in->tm_mday = 0;
913 itm_in->tm_usec = 0;
914
915 if (typmod >= 0)
916 range = INTERVAL_RANGE(typmod);
917 else
918 range = INTERVAL_FULL_RANGE;
919
920 dterr = ParseDateTime(str, workbuf, sizeof(workbuf), field,
921 ftype, MAXDATEFIELDS, &nf);
922 if (dterr == 0)
923 dterr = DecodeInterval(field, ftype, nf, range,
924 &dtype, itm_in);
925
926 /* if those functions think it's a bad format, try ISO8601 style */
927 if (dterr == DTERR_BAD_FORMAT)
928 dterr = DecodeISO8601Interval(str,
929 &dtype, itm_in);
930
931 if (dterr != 0)
932 {
933 if (dterr == DTERR_FIELD_OVERFLOW)
934 dterr = DTERR_INTERVAL_OVERFLOW;
935 DateTimeParseError(dterr, &extra, str, "interval", escontext);
936 PG_RETURN_NULL();
937 }
938
939 result = (Interval *) palloc(sizeof(Interval));
940
941 switch (dtype)
942 {
943 case DTK_DELTA:
944 if (itmin2interval(itm_in, result) != 0)
945 ereturn(escontext, (Datum) 0,
946 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
947 errmsg("interval out of range")));
948 break;
949
950 case DTK_LATE:
951 INTERVAL_NOEND(result);
952 break;
953
954 case DTK_EARLY:
955 INTERVAL_NOBEGIN(result);
956 break;
957
958 default:
959 elog(ERROR, "unexpected dtype %d while parsing interval \"%s\"",
960 dtype, str);
961 }
962
963 AdjustIntervalForTypmod(result, typmod, escontext);
964
965 PG_RETURN_INTERVAL_P(result);
966 }
967
968 /* interval_out()
969 * Convert a time span to external form.
970 */
971 Datum
972 interval_out(PG_FUNCTION_ARGS)
973 {
974 Interval *span = PG_GETARG_INTERVAL_P(0);
975 char *result;
976 struct pg_itm tt,
977 *itm = &tt;
978 char buf[MAXDATELEN + 1];
979
980 if (INTERVAL_NOT_FINITE(span))
981 EncodeSpecialInterval(span, buf);
982 else
983 {
984 interval2itm(*span, itm);
985 EncodeInterval(itm, IntervalStyle, buf);
986 }
987
988 result = pstrdup(buf);
989 PG_RETURN_CSTRING(result);
990 }
991
992 /*
993 * interval_recv - converts external binary format to interval
994 */
995 Datum
996 interval_recv(PG_FUNCTION_ARGS)
997 {
998 StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
999
1000 #ifdef NOT_USED
1001 Oid typelem = PG_GETARG_OID(1);
1002 #endif
1003 int32 typmod = PG_GETARG_INT32(2);
1004 Interval *interval;
1005
1006 interval = (Interval *) palloc(sizeof(Interval));
1007
1008 interval->time = pq_getmsgint64(buf);
1009 interval->day = pq_getmsgint(buf, sizeof(interval->day));
1010 interval->month = pq_getmsgint(buf, sizeof(interval->month));
1011
1012 AdjustIntervalForTypmod(interval, typmod, NULL);
1013
1014 PG_RETURN_INTERVAL_P(interval);
1015 }
1016
1017 /*
1018 * interval_send - converts interval to binary format
1019 */
1020 Datum
1021 interval_send(PG_FUNCTION_ARGS)
1022 {
1023 Interval *interval = PG_GETARG_INTERVAL_P(0);
1024 StringInfoData buf;
1025
1026 pq_begintypsend(&buf);
1027 pq_sendint64(&buf, interval->time);
1028 pq_sendint32(&buf, interval->day);
1029 pq_sendint32(&buf, interval->month);
1030 PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
1031 }
1032
1033 /*
1034 * The interval typmod stores a "range" in its high 16 bits and a "precision"
1035 * in its low 16 bits. Both contribute to defining the resolution of the
1036 * type. Range addresses resolution granules larger than one second, and
1037 * precision specifies resolution below one second. This representation can
1038 * express all SQL standard resolutions, but we implement them all in terms of
1039 * truncating rightward from some position. Range is a bitmap of permitted
1040 * fields, but only the temporally-smallest such field is significant to our
1041 * calculations. Precision is a count of sub-second decimal places to retain.
1042 * Setting all bits (INTERVAL_FULL_PRECISION) gives the same truncation
1043 * semantics as choosing MAX_INTERVAL_PRECISION.
1044 */
1045 Datum
1046 intervaltypmodin(PG_FUNCTION_ARGS)
1047 {
1048 ArrayType *ta = PG_GETARG_ARRAYTYPE_P(0);
1049 int32 *tl;
1050 int n;
1051 int32 typmod;
1052
1053 tl = ArrayGetIntegerTypmods(ta, &n);
1054
1055 /*
1056 * tl[0] - interval range (fields bitmask) tl[1] - precision (optional)
1057 *
1058 * Note we must validate tl[0] even though it's normally guaranteed
1059 * correct by the grammar --- consider SELECT 'foo'::"interval"(1000).
1060 */
1061 if (n > 0)
1062 {
1063 switch (tl[0])
1064 {
1065 case INTERVAL_MASK(YEAR):
1066 case INTERVAL_MASK(MONTH):
1067 case INTERVAL_MASK(DAY):
1068 case INTERVAL_MASK(HOUR):
1069 case INTERVAL_MASK(MINUTE):
1070 case INTERVAL_MASK(SECOND):
1071 case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1072 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1073 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1074 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1075 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1076 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1077 case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1078 case INTERVAL_FULL_RANGE:
1079 /* all OK */
1080 break;
1081 default:
1082 ereport(ERROR,
1083 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1084 errmsg("invalid INTERVAL type modifier")));
1085 }
1086 }
1087
1088 if (n == 1)
1089 {
1090 if (tl[0] != INTERVAL_FULL_RANGE)
1091 typmod = INTERVAL_TYPMOD(INTERVAL_FULL_PRECISION, tl[0]);
1092 else
1093 typmod = -1;
1094 }
1095 else if (n == 2)
1096 {
1097 if (tl[1] < 0)
1098 ereport(ERROR,
1099 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1100 errmsg("INTERVAL(%d) precision must not be negative",
1101 tl[1])));
1102 if (tl[1] > MAX_INTERVAL_PRECISION)
1103 {
1104 ereport(WARNING,
1105 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1106 errmsg("INTERVAL(%d) precision reduced to maximum allowed, %d",
1107 tl[1], MAX_INTERVAL_PRECISION)));
1108 typmod = INTERVAL_TYPMOD(MAX_INTERVAL_PRECISION, tl[0]);
1109 }
1110 else
1111 typmod = INTERVAL_TYPMOD(tl[1], tl[0]);
1112 }
1113 else
1114 {
1115 ereport(ERROR,
1116 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1117 errmsg("invalid INTERVAL type modifier")));
1118 typmod = 0; /* keep compiler quiet */
1119 }
1120
1121 PG_RETURN_INT32(typmod);
1122 }
1123
1124 Datum
1125 intervaltypmodout(PG_FUNCTION_ARGS)
1126 {
1127 int32 typmod = PG_GETARG_INT32(0);
1128 char *res = (char *) palloc(64);
1129 int fields;
1130 int precision;
1131 const char *fieldstr;
1132
1133 if (typmod < 0)
1134 {
1135 *res = '\0';
1136 PG_RETURN_CSTRING(res);
1137 }
1138
1139 fields = INTERVAL_RANGE(typmod);
1140 precision = INTERVAL_PRECISION(typmod);
1141
1142 switch (fields)
1143 {
1144 case INTERVAL_MASK(YEAR):
1145 fieldstr = " year";
1146 break;
1147 case INTERVAL_MASK(MONTH):
1148 fieldstr = " month";
1149 break;
1150 case INTERVAL_MASK(DAY):
1151 fieldstr = " day";
1152 break;
1153 case INTERVAL_MASK(HOUR):
1154 fieldstr = " hour";
1155 break;
1156 case INTERVAL_MASK(MINUTE):
1157 fieldstr = " minute";
1158 break;
1159 case INTERVAL_MASK(SECOND):
1160 fieldstr = " second";
1161 break;
1162 case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1163 fieldstr = " year to month";
1164 break;
1165 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1166 fieldstr = " day to hour";
1167 break;
1168 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1169 fieldstr = " day to minute";
1170 break;
1171 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1172 fieldstr = " day to second";
1173 break;
1174 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1175 fieldstr = " hour to minute";
1176 break;
1177 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1178 fieldstr = " hour to second";
1179 break;
1180 case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1181 fieldstr = " minute to second";
1182 break;
1183 case INTERVAL_FULL_RANGE:
1184 fieldstr = "";
1185 break;
1186 default:
1187 elog(ERROR, "invalid INTERVAL typmod: 0x%x", typmod);
1188 fieldstr = "";
1189 break;
1190 }
1191
1192 if (precision != INTERVAL_FULL_PRECISION)
1193 snprintf(res, 64, "%s(%d)", fieldstr, precision);
1194 else
1195 snprintf(res, 64, "%s", fieldstr);
1196
1197 PG_RETURN_CSTRING(res);
1198 }
1199
1200 /*
1201 * Given an interval typmod value, return a code for the least-significant
1202 * field that the typmod allows to be nonzero, for instance given
1203 * INTERVAL DAY TO HOUR we want to identify "hour".
1204 *
1205 * The results should be ordered by field significance, which means
1206 * we can't use the dt.h macros YEAR etc, because for some odd reason
1207 * they aren't ordered that way. Instead, arbitrarily represent
1208 * SECOND = 0, MINUTE = 1, HOUR = 2, DAY = 3, MONTH = 4, YEAR = 5.
1209 */
1210 static int
1211 intervaltypmodleastfield(int32 typmod)
1212 {
1213 if (typmod < 0)
1214 return 0; /* SECOND */
1215
1216 switch (INTERVAL_RANGE(typmod))
1217 {
1218 case INTERVAL_MASK(YEAR):
1219 return 5; /* YEAR */
1220 case INTERVAL_MASK(MONTH):
1221 return 4; /* MONTH */
1222 case INTERVAL_MASK(DAY):
1223 return 3; /* DAY */
1224 case INTERVAL_MASK(HOUR):
1225 return 2; /* HOUR */
1226 case INTERVAL_MASK(MINUTE):
1227 return 1; /* MINUTE */
1228 case INTERVAL_MASK(SECOND):
1229 return 0; /* SECOND */
1230 case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
1231 return 4; /* MONTH */
1232 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
1233 return 2; /* HOUR */
1234 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1235 return 1; /* MINUTE */
1236 case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1237 return 0; /* SECOND */
1238 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
1239 return 1; /* MINUTE */
1240 case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1241 return 0; /* SECOND */
1242 case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
1243 return 0; /* SECOND */
1244 case INTERVAL_FULL_RANGE:
1245 return 0; /* SECOND */
1246 default:
1247 elog(ERROR, "invalid INTERVAL typmod: 0x%x", typmod);
1248 break;
1249 }
1250 return 0; /* can't get here, but keep compiler quiet */
1251 }
1252
1253
1254 /*
1255 * interval_support()
1256 *
1257 * Planner support function for interval_scale().
1258 *
1259 * Flatten superfluous calls to interval_scale(). The interval typmod is
1260 * complex to permit accepting and regurgitating all SQL standard variations.
1261 * For truncation purposes, it boils down to a single, simple granularity.
1262 */
1263 Datum
1264 interval_support(PG_FUNCTION_ARGS)
1265 {
1266 Node *rawreq = (Node *) PG_GETARG_POINTER(0);
1267 Node *ret = NULL;
1268
1269 if (IsA(rawreq, SupportRequestSimplify))
1270 {
1271 SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
1272 FuncExpr *expr = req->fcall;
1273 Node *typmod;
1274
1275 Assert(list_length(expr->args) >= 2);
1276
1277 typmod = (Node *) lsecond(expr->args);
1278
1279 if (IsA(typmod, Const) && !((Const *) typmod)->constisnull)
1280 {
1281 Node *source = (Node *) linitial(expr->args);
1282 int32 new_typmod = DatumGetInt32(((Const *) typmod)->constvalue);
1283 bool noop;
1284
1285 if (new_typmod < 0)
1286 noop = true;
1287 else
1288 {
1289 int32 old_typmod = exprTypmod(source);
1290 int old_least_field;
1291 int new_least_field;
1292 int old_precis;
1293 int new_precis;
1294
1295 old_least_field = intervaltypmodleastfield(old_typmod);
1296 new_least_field = intervaltypmodleastfield(new_typmod);
1297 if (old_typmod < 0)
1298 old_precis = INTERVAL_FULL_PRECISION;
1299 else
1300 old_precis = INTERVAL_PRECISION(old_typmod);
1301 new_precis = INTERVAL_PRECISION(new_typmod);
1302
1303 /*
1304 * Cast is a no-op if least field stays the same or decreases
1305 * while precision stays the same or increases. But
1306 * precision, which is to say, sub-second precision, only
1307 * affects ranges that include SECOND.
1308 */
1309 noop = (new_least_field <= old_least_field) &&
1310 (old_least_field > 0 /* SECOND */ ||
1311 new_precis >= MAX_INTERVAL_PRECISION ||
1312 new_precis >= old_precis);
1313 }
1314 if (noop)
1315 ret = relabel_to_typmod(source, new_typmod);
1316 }
1317 }
1318
1319 PG_RETURN_POINTER(ret);
1320 }
1321
1322 /* interval_scale()
1323 * Adjust interval type for specified fields.
1324 * Used by PostgreSQL type system to stuff columns.
1325 */
1326 Datum
1327 interval_scale(PG_FUNCTION_ARGS)
1328 {
1329 Interval *interval = PG_GETARG_INTERVAL_P(0);
1330 int32 typmod = PG_GETARG_INT32(1);
1331 Interval *result;
1332
1333 result = palloc(sizeof(Interval));
1334 *result = *interval;
1335
1336 AdjustIntervalForTypmod(result, typmod, NULL);
1337
1338 PG_RETURN_INTERVAL_P(result);
1339 }
1340
1341 /*
1342 * Adjust interval for specified precision, in both YEAR to SECOND
1343 * range and sub-second precision.
1344 *
1345 * Returns true on success, false on failure (if escontext points to an
1346 * ErrorSaveContext; otherwise errors are thrown).
1347 */
1348 static bool
1349 AdjustIntervalForTypmod(Interval *interval, int32 typmod,
1350 Node *escontext)
1351 {
1352 static const int64 IntervalScales[MAX_INTERVAL_PRECISION + 1] = {
1353 INT64CONST(1000000),
1354 INT64CONST(100000),
1355 INT64CONST(10000),
1356 INT64CONST(1000),
1357 INT64CONST(100),
1358 INT64CONST(10),
1359 INT64CONST(1)
1360 };
1361
1362 static const int64 IntervalOffsets[MAX_INTERVAL_PRECISION + 1] = {
1363 INT64CONST(500000),
1364 INT64CONST(50000),
1365 INT64CONST(5000),
1366 INT64CONST(500),
1367 INT64CONST(50),
1368 INT64CONST(5),
1369 INT64CONST(0)
1370 };
1371
1372 /* Typmod has no effect on infinite intervals */
1373 if (INTERVAL_NOT_FINITE(interval))
1374 return true;
1375
1376 /*
1377 * Unspecified range and precision? Then not necessary to adjust. Setting
1378 * typmod to -1 is the convention for all data types.
1379 */
1380 if (typmod >= 0)
1381 {
1382 int range = INTERVAL_RANGE(typmod);
1383 int precision = INTERVAL_PRECISION(typmod);
1384
1385 /*
1386 * Our interpretation of intervals with a limited set of fields is
1387 * that fields to the right of the last one specified are zeroed out,
1388 * but those to the left of it remain valid. Thus for example there
1389 * is no operational difference between INTERVAL YEAR TO MONTH and
1390 * INTERVAL MONTH. In some cases we could meaningfully enforce that
1391 * higher-order fields are zero; for example INTERVAL DAY could reject
1392 * nonzero "month" field. However that seems a bit pointless when we
1393 * can't do it consistently. (We cannot enforce a range limit on the
1394 * highest expected field, since we do not have any equivalent of
1395 * SQL's <interval leading field precision>.) If we ever decide to
1396 * revisit this, interval_support will likely require adjusting.
1397 *
1398 * Note: before PG 8.4 we interpreted a limited set of fields as
1399 * actually causing a "modulo" operation on a given value, potentially
1400 * losing high-order as well as low-order information. But there is
1401 * no support for such behavior in the standard, and it seems fairly
1402 * undesirable on data consistency grounds anyway. Now we only
1403 * perform truncation or rounding of low-order fields.
1404 */
1405 if (range == INTERVAL_FULL_RANGE)
1406 {
1407 /* Do nothing... */
1408 }
1409 else if (range == INTERVAL_MASK(YEAR))
1410 {
1411 interval->month = (interval->month / MONTHS_PER_YEAR) * MONTHS_PER_YEAR;
1412 interval->day = 0;
1413 interval->time = 0;
1414 }
1415 else if (range == INTERVAL_MASK(MONTH))
1416 {
1417 interval->day = 0;
1418 interval->time = 0;
1419 }
1420 /* YEAR TO MONTH */
1421 else if (range == (INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH)))
1422 {
1423 interval->day = 0;
1424 interval->time = 0;
1425 }
1426 else if (range == INTERVAL_MASK(DAY))
1427 {
1428 interval->time = 0;
1429 }
1430 else if (range == INTERVAL_MASK(HOUR))
1431 {
1432 interval->time = (interval->time / USECS_PER_HOUR) *
1433 USECS_PER_HOUR;
1434 }
1435 else if (range == INTERVAL_MASK(MINUTE))
1436 {
1437 interval->time = (interval->time / USECS_PER_MINUTE) *
1438 USECS_PER_MINUTE;
1439 }
1440 else if (range == INTERVAL_MASK(SECOND))
1441 {
1442 /* fractional-second rounding will be dealt with below */
1443 }
1444 /* DAY TO HOUR */
1445 else if (range == (INTERVAL_MASK(DAY) |
1446 INTERVAL_MASK(HOUR)))
1447 {
1448 interval->time = (interval->time / USECS_PER_HOUR) *
1449 USECS_PER_HOUR;
1450 }
1451 /* DAY TO MINUTE */
1452 else if (range == (INTERVAL_MASK(DAY) |
1453 INTERVAL_MASK(HOUR) |
1454 INTERVAL_MASK(MINUTE)))
1455 {
1456 interval->time = (interval->time / USECS_PER_MINUTE) *
1457 USECS_PER_MINUTE;
1458 }
1459 /* DAY TO SECOND */
1460 else if (range == (INTERVAL_MASK(DAY) |
1461 INTERVAL_MASK(HOUR) |
1462 INTERVAL_MASK(MINUTE) |
1463 INTERVAL_MASK(SECOND)))
1464 {
1465 /* fractional-second rounding will be dealt with below */
1466 }
1467 /* HOUR TO MINUTE */
1468 else if (range == (INTERVAL_MASK(HOUR) |
1469 INTERVAL_MASK(MINUTE)))
1470 {
1471 interval->time = (interval->time / USECS_PER_MINUTE) *
1472 USECS_PER_MINUTE;
1473 }
1474 /* HOUR TO SECOND */
1475 else if (range == (INTERVAL_MASK(HOUR) |
1476 INTERVAL_MASK(MINUTE) |
1477 INTERVAL_MASK(SECOND)))
1478 {
1479 /* fractional-second rounding will be dealt with below */
1480 }
1481 /* MINUTE TO SECOND */
1482 else if (range == (INTERVAL_MASK(MINUTE) |
1483 INTERVAL_MASK(SECOND)))
1484 {
1485 /* fractional-second rounding will be dealt with below */
1486 }
1487 else
1488 elog(ERROR, "unrecognized interval typmod: %d", typmod);
1489
1490 /* Need to adjust sub-second precision? */
1491 if (precision != INTERVAL_FULL_PRECISION)
1492 {
1493 if (precision < 0 || precision > MAX_INTERVAL_PRECISION)
1494 ereturn(escontext, false,
1495 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1496 errmsg("interval(%d) precision must be between %d and %d",
1497 precision, 0, MAX_INTERVAL_PRECISION)));
1498
1499 if (interval->time >= INT64CONST(0))
1500 {
1501 if (pg_add_s64_overflow(interval->time,
1502 IntervalOffsets[precision],
1503 &interval->time))
1504 ereturn(escontext, false,
1505 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1506 errmsg("interval out of range")));
1507 interval->time -= interval->time % IntervalScales[precision];
1508 }
1509 else
1510 {
1511 if (pg_sub_s64_overflow(interval->time,
1512 IntervalOffsets[precision],
1513 &interval->time))
1514 ereturn(escontext, false,
1515 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1516 errmsg("interval out of range")));
1517 interval->time -= interval->time % IntervalScales[precision];
1518 }
1519 }
1520 }
1521
1522 return true;
1523 }
1524
1525 /*
1526 * make_interval - numeric Interval constructor
1527 */
1528 Datum
1529 make_interval(PG_FUNCTION_ARGS)
1530 {
1531 int32 years = PG_GETARG_INT32(0);
1532 int32 months = PG_GETARG_INT32(1);
1533 int32 weeks = PG_GETARG_INT32(2);
1534 int32 days = PG_GETARG_INT32(3);
1535 int32 hours = PG_GETARG_INT32(4);
1536 int32 mins = PG_GETARG_INT32(5);
1537 double secs = PG_GETARG_FLOAT8(6);
1538 Interval *result;
1539
1540 /*
1541 * Reject out-of-range inputs. We reject any input values that cause
1542 * integer overflow of the corresponding interval fields.
1543 */
1544 if (isinf(secs) || isnan(secs))
1545 goto out_of_range;
1546
1547 result = (Interval *) palloc(sizeof(Interval));
1548
1549 /* years and months -> months */
1550 if (pg_mul_s32_overflow(years, MONTHS_PER_YEAR, &result->month) ||
1551 pg_add_s32_overflow(result->month, months, &result->month))
1552 goto out_of_range;
1553
1554 /* weeks and days -> days */
1555 if (pg_mul_s32_overflow(weeks, DAYS_PER_WEEK, &result->day) ||
1556 pg_add_s32_overflow(result->day, days, &result->day))
1557 goto out_of_range;
1558
1559 /* hours and mins -> usecs (cannot overflow 64-bit) */
1560 result->time = hours * USECS_PER_HOUR + mins * USECS_PER_MINUTE;
1561
1562 /* secs -> usecs */
1563 secs = rint(float8_mul(secs, USECS_PER_SEC));
1564 if (!FLOAT8_FITS_IN_INT64(secs) ||
1565 pg_add_s64_overflow(result->time, (int64) secs, &result->time))
1566 goto out_of_range;
1567
1568 /* make sure that the result is finite */
1569 if (INTERVAL_NOT_FINITE(result))
1570 goto out_of_range;
1571
1572 PG_RETURN_INTERVAL_P(result);
1573
1574 out_of_range:
1575 ereport(ERROR,
1576 errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
1577 errmsg("interval out of range"));
1578
1579 PG_RETURN_NULL(); /* keep compiler quiet */
1580 }
1581
1582 /* EncodeSpecialTimestamp()
1583 * Convert reserved timestamp data type to string.
1584 */
1585 void
1586 EncodeSpecialTimestamp(Timestamp dt, char *str)
1587 {
1588 if (TIMESTAMP_IS_NOBEGIN(dt))
1589 strcpy(str, EARLY);
1590 else if (TIMESTAMP_IS_NOEND(dt))
1591 strcpy(str, LATE);
1592 else /* shouldn't happen */
1593 elog(ERROR, "invalid argument for EncodeSpecialTimestamp");
1594 }
1595
1596 static void
1597 EncodeSpecialInterval(const Interval *interval, char *str)
1598 {
1599 if (INTERVAL_IS_NOBEGIN(interval))
1600 strcpy(str, EARLY);
1601 else if (INTERVAL_IS_NOEND(interval))
1602 strcpy(str, LATE);
1603 else /* shouldn't happen */
1604 elog(ERROR, "invalid argument for EncodeSpecialInterval");
1605 }
1606
1607 Datum
1608 now(PG_FUNCTION_ARGS)
1609 {
1610 PG_RETURN_TIMESTAMPTZ(GetCurrentTransactionStartTimestamp());
1611 }
1612
1613 Datum
1614 statement_timestamp(PG_FUNCTION_ARGS)
1615 {
1616 PG_RETURN_TIMESTAMPTZ(GetCurrentStatementStartTimestamp());
1617 }
1618
1619 Datum
1620 clock_timestamp(PG_FUNCTION_ARGS)
1621 {
1622 PG_RETURN_TIMESTAMPTZ(GetCurrentTimestamp());
1623 }
1624
1625 Datum
1626 pg_postmaster_start_time(PG_FUNCTION_ARGS)
1627 {
1628 PG_RETURN_TIMESTAMPTZ(PgStartTime);
1629 }
1630
1631 Datum
1632 pg_conf_load_time(PG_FUNCTION_ARGS)
1633 {
1634 PG_RETURN_TIMESTAMPTZ(PgReloadTime);
1635 }
1636
1637 /*
1638 * GetCurrentTimestamp -- get the current operating system time
1639 *
1640 * Result is in the form of a TimestampTz value, and is expressed to the
1641 * full precision of the gettimeofday() syscall
1642 */
1643 TimestampTz
1644 GetCurrentTimestamp(void)
1645 {
1646 TimestampTz result;
1647 struct timeval tp;
1648
1649 gettimeofday(&tp, NULL);
1650
1651 result = (TimestampTz) tp.tv_sec -
1652 ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
1653 result = (result * USECS_PER_SEC) + tp.tv_usec;
1654
1655 return result;
1656 }
1657
1658 /*
1659 * GetSQLCurrentTimestamp -- implements CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(n)
1660 */
1661 TimestampTz
1662 GetSQLCurrentTimestamp(int32 typmod)
1663 {
1664 TimestampTz ts;
1665
1666 ts = GetCurrentTransactionStartTimestamp();
1667 if (typmod >= 0)
1668 AdjustTimestampForTypmod(&ts, typmod, NULL);
1669 return ts;
1670 }
1671
1672 /*
1673 * GetSQLLocalTimestamp -- implements LOCALTIMESTAMP, LOCALTIMESTAMP(n)
1674 */
1675 Timestamp
1676 GetSQLLocalTimestamp(int32 typmod)
1677 {
1678 Timestamp ts;
1679
1680 ts = timestamptz2timestamp(GetCurrentTransactionStartTimestamp());
1681 if (typmod >= 0)
1682 AdjustTimestampForTypmod(&ts, typmod, NULL);
1683 return ts;
1684 }
1685
1686 /*
1687 * timeofday(*) -- returns the current time as a text.
1688 */
1689 Datum
1690 timeofday(PG_FUNCTION_ARGS)
1691 {
1692 struct timeval tp;
1693 char templ[128];
1694 char buf[128];
1695 pg_time_t tt;
1696
1697 gettimeofday(&tp, NULL);
1698 tt = (pg_time_t) tp.tv_sec;
1699 pg_strftime(templ, sizeof(templ), "%a %b %d %H:%M:%S.%%06d %Y %Z",
1700 pg_localtime(&tt, session_timezone));
1701 snprintf(buf, sizeof(buf), templ, tp.tv_usec);
1702
1703 PG_RETURN_TEXT_P(cstring_to_text(buf));
1704 }
1705
1706 /*
1707 * TimestampDifference -- convert the difference between two timestamps
1708 * into integer seconds and microseconds
1709 *
1710 * This is typically used to calculate a wait timeout for select(2),
1711 * which explains the otherwise-odd choice of output format.
1712 *
1713 * Both inputs must be ordinary finite timestamps (in current usage,
1714 * they'll be results from GetCurrentTimestamp()).
1715 *
1716 * We expect start_time <= stop_time. If not, we return zeros,
1717 * since then we're already past the previously determined stop_time.
1718 */
1719 void
1720 TimestampDifference(TimestampTz start_time, TimestampTz stop_time,
1721 long *secs, int *microsecs)
1722 {
1723 TimestampTz diff = stop_time - start_time;
1724
1725 if (diff <= 0)
1726 {
1727 *secs = 0;
1728 *microsecs = 0;
1729 }
1730 else
1731 {
1732 *secs = (long) (diff / USECS_PER_SEC);
1733 *microsecs = (int) (diff % USECS_PER_SEC);
1734 }
1735 }
1736
1737 /*
1738 * TimestampDifferenceMilliseconds -- convert the difference between two
1739 * timestamps into integer milliseconds
1740 *
1741 * This is typically used to calculate a wait timeout for WaitLatch()
1742 * or a related function. The choice of "long" as the result type
1743 * is to harmonize with that; furthermore, we clamp the result to at most
1744 * INT_MAX milliseconds, because that's all that WaitLatch() allows.
1745 *
1746 * We expect start_time <= stop_time. If not, we return zero,
1747 * since then we're already past the previously determined stop_time.
1748 *
1749 * Subtracting finite and infinite timestamps works correctly, returning
1750 * zero or INT_MAX as appropriate.
1751 *
1752 * Note we round up any fractional millisecond, since waiting for just
1753 * less than the intended timeout is undesirable.
1754 */
1755 long
1756 TimestampDifferenceMilliseconds(TimestampTz start_time, TimestampTz stop_time)
1757 {
1758 TimestampTz diff;
1759
1760 /* Deal with zero or negative elapsed time quickly. */
1761 if (start_time >= stop_time)
1762 return 0;
1763 /* To not fail with timestamp infinities, we must detect overflow. */
1764 if (pg_sub_s64_overflow(stop_time, start_time, &diff))
1765 return (long) INT_MAX;
1766 if (diff >= (INT_MAX * INT64CONST(1000) - 999))
1767 return (long) INT_MAX;
1768 else
1769 return (long) ((diff + 999) / 1000);
1770 }
1771
1772 /*
1773 * TimestampDifferenceExceeds -- report whether the difference between two
1774 * timestamps is >= a threshold (expressed in milliseconds)
1775 *
1776 * Both inputs must be ordinary finite timestamps (in current usage,
1777 * they'll be results from GetCurrentTimestamp()).
1778 */
1779 bool
1780 TimestampDifferenceExceeds(TimestampTz start_time,
1781 TimestampTz stop_time,
1782 int msec)
1783 {
1784 TimestampTz diff = stop_time - start_time;
1785
1786 return (diff >= msec * INT64CONST(1000));
1787 }
1788
1789 /*
1790 * Convert a time_t to TimestampTz.
1791 *
1792 * We do not use time_t internally in Postgres, but this is provided for use
1793 * by functions that need to interpret, say, a stat(2) result.
1794 *
1795 * To avoid having the function's ABI vary depending on the width of time_t,
1796 * we declare the argument as pg_time_t, which is cast-compatible with
1797 * time_t but always 64 bits wide (unless the platform has no 64-bit type).
1798 * This detail should be invisible to callers, at least at source code level.
1799 */
1800 TimestampTz
1801 time_t_to_timestamptz(pg_time_t tm)
1802 {
1803 TimestampTz result;
1804
1805 result = (TimestampTz) tm -
1806 ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
1807 result *= USECS_PER_SEC;
1808
1809 return result;
1810 }
1811
1812 /*
1813 * Convert a TimestampTz to time_t.
1814 *
1815 * This too is just marginally useful, but some places need it.
1816 *
1817 * To avoid having the function's ABI vary depending on the width of time_t,
1818 * we declare the result as pg_time_t, which is cast-compatible with
1819 * time_t but always 64 bits wide (unless the platform has no 64-bit type).
1820 * This detail should be invisible to callers, at least at source code level.
1821 */
1822 pg_time_t
1823 timestamptz_to_time_t(TimestampTz t)
1824 {
1825 pg_time_t result;
1826
1827 result = (pg_time_t) (t / USECS_PER_SEC +
1828 ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY));
1829
1830 return result;
1831 }
1832
1833 /*
1834 * Produce a C-string representation of a TimestampTz.
1835 *
1836 * This is mostly for use in emitting messages. The primary difference
1837 * from timestamptz_out is that we force the output format to ISO. Note
1838 * also that the result is in a static buffer, not pstrdup'd.
1839 *
1840 * See also pg_strftime.
1841 */
1842 const char *
1843 timestamptz_to_str(TimestampTz t)
1844 {
1845 static char buf[MAXDATELEN + 1];
1846 int tz;
1847 struct pg_tm tt,
1848 *tm = &tt;
1849 fsec_t fsec;
1850 const char *tzn;
1851
1852 if (TIMESTAMP_NOT_FINITE(t))
1853 EncodeSpecialTimestamp(t, buf);
1854 else if (timestamp2tm(t, &tz, tm, &fsec, &tzn, NULL) == 0)
1855 EncodeDateTime(tm, fsec, true, tz, tzn, USE_ISO_DATES, buf);
1856 else
1857 strlcpy(buf, "(timestamp out of range)", sizeof(buf));
1858
1859 return buf;
1860 }
1861
1862
1863 void
1864 dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec)
1865 {
1866 TimeOffset time;
1867
1868 time = jd;
1869
1870 *hour = time / USECS_PER_HOUR;
1871 time -= (*hour) * USECS_PER_HOUR;
1872 *min = time / USECS_PER_MINUTE;
1873 time -= (*min) * USECS_PER_MINUTE;
1874 *sec = time / USECS_PER_SEC;
1875 *fsec = time - (*sec * USECS_PER_SEC);
1876 } /* dt2time() */
1877
1878
1879 /*
1880 * timestamp2tm() - Convert timestamp data type to POSIX time structure.
1881 *
1882 * Note that year is _not_ 1900-based, but is an explicit full value.
1883 * Also, month is one-based, _not_ zero-based.
1884 * Returns:
1885 * 0 on success
1886 * -1 on out of range
1887 *
1888 * If attimezone is NULL, the global timezone setting will be used.
1889 */
1890 int
1891 timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm, fsec_t *fsec, const char **tzn, pg_tz *attimezone)
1892 {
1893 Timestamp date;
1894 Timestamp time;
1895 pg_time_t utime;
1896
1897 /* Use session timezone if caller asks for default */
1898 if (attimezone == NULL)
1899 attimezone = session_timezone;
1900
1901 time = dt;
1902 TMODULO(time, date, USECS_PER_DAY);
1903
1904 if (time < INT64CONST(0))
1905 {
1906 time += USECS_PER_DAY;
1907 date -= 1;
1908 }
1909
1910 /* add offset to go from J2000 back to standard Julian date */
1911 date += POSTGRES_EPOCH_JDATE;
1912
1913 /* Julian day routine does not work for negative Julian days */
1914 if (date < 0 || date > (Timestamp) INT_MAX)
1915 return -1;
1916
1917 j2date((int) date, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
1918 dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
1919
1920 /* Done if no TZ conversion wanted */
1921 if (tzp == NULL)
1922 {
1923 tm->tm_isdst = -1;
1924 tm->tm_gmtoff = 0;
1925 tm->tm_zone = NULL;
1926 if (tzn != NULL)
1927 *tzn = NULL;
1928 return 0;
1929 }
1930
1931 /*
1932 * If the time falls within the range of pg_time_t, use pg_localtime() to
1933 * rotate to the local time zone.
1934 *
1935 * First, convert to an integral timestamp, avoiding possibly
1936 * platform-specific roundoff-in-wrong-direction errors, and adjust to
1937 * Unix epoch. Then see if we can convert to pg_time_t without loss. This
1938 * coding avoids hardwiring any assumptions about the width of pg_time_t,
1939 * so it should behave sanely on machines without int64.
1940 */
1941 dt = (dt - *fsec) / USECS_PER_SEC +
1942 (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY;
1943 utime = (pg_time_t) dt;
1944 if ((Timestamp) utime == dt)
1945 {
1946 struct pg_tm *tx = pg_localtime(&utime, attimezone);
1947
1948 tm->tm_year = tx->tm_year + 1900;
1949 tm->tm_mon = tx->tm_mon + 1;
1950 tm->tm_mday = tx->tm_mday;
1951 tm->tm_hour = tx->tm_hour;
1952 tm->tm_min = tx->tm_min;
1953 tm->tm_sec = tx->tm_sec;
1954 tm->tm_isdst = tx->tm_isdst;
1955 tm->tm_gmtoff = tx->tm_gmtoff;
1956 tm->tm_zone = tx->tm_zone;
1957 *tzp = -tm->tm_gmtoff;
1958 if (tzn != NULL)
1959 *tzn = tm->tm_zone;
1960 }
1961 else
1962 {
1963 /*
1964 * When out of range of pg_time_t, treat as GMT
1965 */
1966 *tzp = 0;
1967 /* Mark this as *no* time zone available */
1968 tm->tm_isdst = -1;
1969 tm->tm_gmtoff = 0;
1970 tm->tm_zone = NULL;
1971 if (tzn != NULL)
1972 *tzn = NULL;
1973 }
1974
1975 return 0;
1976 }
1977
1978
1979 /* tm2timestamp()
1980 * Convert a tm structure to a timestamp data type.
1981 * Note that year is _not_ 1900-based, but is an explicit full value.
1982 * Also, month is one-based, _not_ zero-based.
1983 *
1984 * Returns -1 on failure (value out of range).
1985 */
1986 int
1987 tm2timestamp(struct pg_tm *tm, fsec_t fsec, int *tzp, Timestamp *result)
1988 {
1989 TimeOffset date;
1990 TimeOffset time;
1991
1992 /* Prevent overflow in Julian-day routines */
1993 if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
1994 {
1995 *result = 0; /* keep compiler quiet */
1996 return -1;
1997 }
1998
1999 date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
2000 time = time2t(tm->tm_hour, tm->tm_min, tm->tm_sec, fsec);
2001
2002 if (unlikely(pg_mul_s64_overflow(date, USECS_PER_DAY, result) ||
2003 pg_add_s64_overflow(*result, time, result)))
2004 {
2005 *result = 0; /* keep compiler quiet */
2006 return -1;
2007 }
2008 if (tzp != NULL)
2009 *result = dt2local(*result, -(*tzp));
2010
2011 /* final range check catches just-out-of-range timestamps */
2012 if (!IS_VALID_TIMESTAMP(*result))
2013 {
2014 *result = 0; /* keep compiler quiet */
2015 return -1;
2016 }
2017
2018 return 0;
2019 }
2020
2021
2022 /* interval2itm()
2023 * Convert an Interval to a pg_itm structure.
2024 * Note: overflow is not possible, because the pg_itm fields are
2025 * wide enough for all possible conversion results.
2026 */
2027 void
2028 interval2itm(Interval span, struct pg_itm *itm)
2029 {
2030 TimeOffset time;
2031 TimeOffset tfrac;
2032
2033 itm->tm_year = span.month / MONTHS_PER_YEAR;
2034 itm->tm_mon = span.month % MONTHS_PER_YEAR;
2035 itm->tm_mday = span.day;
2036 time = span.time;
2037
2038 tfrac = time / USECS_PER_HOUR;
2039 time -= tfrac * USECS_PER_HOUR;
2040 itm->tm_hour = tfrac;
2041 tfrac = time / USECS_PER_MINUTE;
2042 time -= tfrac * USECS_PER_MINUTE;
2043 itm->tm_min = (int) tfrac;
2044 tfrac = time / USECS_PER_SEC;
2045 time -= tfrac * USECS_PER_SEC;
2046 itm->tm_sec = (int) tfrac;
2047 itm->tm_usec = (int) time;
2048 }
2049
2050 /* itm2interval()
2051 * Convert a pg_itm structure to an Interval.
2052 * Returns 0 if OK, -1 on overflow.
2053 *
2054 * This is for use in computations expected to produce finite results. Any
2055 * inputs that lead to infinite results are treated as overflows.
2056 */
2057 int
2058 itm2interval(struct pg_itm *itm, Interval *span)
2059 {
2060 int64 total_months = (int64) itm->tm_year * MONTHS_PER_YEAR + itm->tm_mon;
2061
2062 if (total_months > INT_MAX || total_months < INT_MIN)
2063 return -1;
2064 span->month = (int32) total_months;
2065 span->day = itm->tm_mday;
2066 if (pg_mul_s64_overflow(itm->tm_hour, USECS_PER_HOUR,
2067 &span->time))
2068 return -1;
2069 /* tm_min, tm_sec are 32 bits, so intermediate products can't overflow */
2070 if (pg_add_s64_overflow(span->time, itm->tm_min * USECS_PER_MINUTE,
2071 &span->time))
2072 return -1;
2073 if (pg_add_s64_overflow(span->time, itm->tm_sec * USECS_PER_SEC,
2074 &span->time))
2075 return -1;
2076 if (pg_add_s64_overflow(span->time, itm->tm_usec,
2077 &span->time))
2078 return -1;
2079 if (INTERVAL_NOT_FINITE(span))
2080 return -1;
2081 return 0;
2082 }
2083
2084 /* itmin2interval()
2085 * Convert a pg_itm_in structure to an Interval.
2086 * Returns 0 if OK, -1 on overflow.
2087 *
2088 * Note: if the result is infinite, it is not treated as an overflow. This
2089 * avoids any dump/reload hazards from pre-17 databases that do not support
2090 * infinite intervals, but do allow finite intervals with all fields set to
2091 * INT_MIN/INT_MAX (outside the documented range). Such intervals will be
2092 * silently converted to +/-infinity. This may not be ideal, but seems
2093 * preferable to failure, and ought to be pretty unlikely in practice.
2094 */
2095 int
2096 itmin2interval(struct pg_itm_in *itm_in, Interval *span)
2097 {
2098 int64 total_months = (int64) itm_in->tm_year * MONTHS_PER_YEAR + itm_in->tm_mon;
2099
2100 if (total_months > INT_MAX || total_months < INT_MIN)
2101 return -1;
2102 span->month = (int32) total_months;
2103 span->day = itm_in->tm_mday;
2104 span->time = itm_in->tm_usec;
2105 return 0;
2106 }
2107
2108 static TimeOffset
2109 time2t(const int hour, const int min, const int sec, const fsec_t fsec)
2110 {
2111 return (((((hour * MINS_PER_HOUR) + min) * SECS_PER_MINUTE) + sec) * USECS_PER_SEC) + fsec;
2112 }
2113
2114 static Timestamp
2115 dt2local(Timestamp dt, int timezone)
2116 {
2117 dt -= (timezone * USECS_PER_SEC);
2118 return dt;
2119 }
2120
2121
2122 /*****************************************************************************
2123 * PUBLIC ROUTINES *
2124 *****************************************************************************/
2125
2126
2127 Datum
2128 timestamp_finite(PG_FUNCTION_ARGS)
2129 {
2130 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2131
2132 PG_RETURN_BOOL(!TIMESTAMP_NOT_FINITE(timestamp));
2133 }
2134
2135 Datum
2136 interval_finite(PG_FUNCTION_ARGS)
2137 {
2138 Interval *interval = PG_GETARG_INTERVAL_P(0);
2139
2140 PG_RETURN_BOOL(!INTERVAL_NOT_FINITE(interval));
2141 }
2142
2143
2144 /*----------------------------------------------------------
2145 * Relational operators for timestamp.
2146 *---------------------------------------------------------*/
2147
2148 void
2149 GetEpochTime(struct pg_tm *tm)
2150 {
2151 struct pg_tm *t0;
2152 pg_time_t epoch = 0;
2153
2154 t0 = pg_gmtime(&epoch);
2155
2156 if (t0 == NULL)
2157 elog(ERROR, "could not convert epoch to timestamp: %m");
2158
2159 tm->tm_year = t0->tm_year;
2160 tm->tm_mon = t0->tm_mon;
2161 tm->tm_mday = t0->tm_mday;
2162 tm->tm_hour = t0->tm_hour;
2163 tm->tm_min = t0->tm_min;
2164 tm->tm_sec = t0->tm_sec;
2165
2166 tm->tm_year += 1900;
2167 tm->tm_mon++;
2168 }
2169
2170 Timestamp
2171 SetEpochTimestamp(void)
2172 {
2173 Timestamp dt;
2174 struct pg_tm tt,
2175 *tm = &tt;
2176
2177 GetEpochTime(tm);
2178 /* we don't bother to test for failure ... */
2179 tm2timestamp(tm, 0, NULL, &dt);
2180
2181 return dt;
2182 } /* SetEpochTimestamp() */
2183
2184 /*
2185 * We are currently sharing some code between timestamp and timestamptz.
2186 * The comparison functions are among them. - thomas 2001-09-25
2187 *
2188 * timestamp_relop - is timestamp1 relop timestamp2
2189 */
2190 int
2191 timestamp_cmp_internal(Timestamp dt1, Timestamp dt2)
2192 {
2193 return (dt1 < dt2) ? -1 : ((dt1 > dt2) ? 1 : 0);
2194 }
2195
2196 Datum
2197 timestamp_eq(PG_FUNCTION_ARGS)
2198 {
2199 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2200 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2201
2202 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0);
2203 }
2204
2205 Datum
2206 timestamp_ne(PG_FUNCTION_ARGS)
2207 {
2208 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2209 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2210
2211 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0);
2212 }
2213
2214 Datum
2215 timestamp_lt(PG_FUNCTION_ARGS)
2216 {
2217 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2218 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2219
2220 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0);
2221 }
2222
2223 Datum
2224 timestamp_gt(PG_FUNCTION_ARGS)
2225 {
2226 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2227 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2228
2229 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0);
2230 }
2231
2232 Datum
2233 timestamp_le(PG_FUNCTION_ARGS)
2234 {
2235 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2236 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2237
2238 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0);
2239 }
2240
2241 Datum
2242 timestamp_ge(PG_FUNCTION_ARGS)
2243 {
2244 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2245 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2246
2247 PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0);
2248 }
2249
2250 Datum
2251 timestamp_cmp(PG_FUNCTION_ARGS)
2252 {
2253 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2254 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2255
2256 PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2));
2257 }
2258
2259 #if SIZEOF_DATUM < 8
2260 /* note: this is used for timestamptz also */
2261 static int
2262 timestamp_fastcmp(Datum x, Datum y, SortSupport ssup)
2263 {
2264 Timestamp a = DatumGetTimestamp(x);
2265 Timestamp b = DatumGetTimestamp(y);
2266
2267 return timestamp_cmp_internal(a, b);
2268 }
2269 #endif
2270
2271 Datum
2272 timestamp_sortsupport(PG_FUNCTION_ARGS)
2273 {
2274 SortSupport ssup = (SortSupport) PG_GETARG_POINTER(0);
2275
2276 #if SIZEOF_DATUM >= 8
2277
2278 /*
2279 * If this build has pass-by-value timestamps, then we can use a standard
2280 * comparator function.
2281 */
2282 ssup->comparator = ssup_datum_signed_cmp;
2283 #else
2284 ssup->comparator = timestamp_fastcmp;
2285 #endif
2286 PG_RETURN_VOID();
2287 }
2288
2289 Datum
2290 timestamp_hash(PG_FUNCTION_ARGS)
2291 {
2292 return hashint8(fcinfo);
2293 }
2294
2295 Datum
2296 timestamp_hash_extended(PG_FUNCTION_ARGS)
2297 {
2298 return hashint8extended(fcinfo);
2299 }
2300
2301 Datum
2302 timestamptz_hash(PG_FUNCTION_ARGS)
2303 {
2304 return hashint8(fcinfo);
2305 }
2306
2307 Datum
2308 timestamptz_hash_extended(PG_FUNCTION_ARGS)
2309 {
2310 return hashint8extended(fcinfo);
2311 }
2312
2313 /*
2314 * Cross-type comparison functions for timestamp vs timestamptz
2315 */
2316
2317 int32
2318 timestamp_cmp_timestamptz_internal(Timestamp timestampVal, TimestampTz dt2)
2319 {
2320 TimestampTz dt1;
2321 int overflow;
2322
2323 dt1 = timestamp2timestamptz_opt_overflow(timestampVal, &overflow);
2324 if (overflow > 0)
2325 {
2326 /* dt1 is larger than any finite timestamp, but less than infinity */
2327 return TIMESTAMP_IS_NOEND(dt2) ? -1 : +1;
2328 }
2329 if (overflow < 0)
2330 {
2331 /* dt1 is less than any finite timestamp, but more than -infinity */
2332 return TIMESTAMP_IS_NOBEGIN(dt2) ? +1 : -1;
2333 }
2334
2335 return timestamptz_cmp_internal(dt1, dt2);
2336 }
2337
2338 Datum
2339 timestamp_eq_timestamptz(PG_FUNCTION_ARGS)
2340 {
2341 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2342 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2343
2344 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) == 0);
2345 }
2346
2347 Datum
2348 timestamp_ne_timestamptz(PG_FUNCTION_ARGS)
2349 {
2350 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2351 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2352
2353 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) != 0);
2354 }
2355
2356 Datum
2357 timestamp_lt_timestamptz(PG_FUNCTION_ARGS)
2358 {
2359 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2360 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2361
2362 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) < 0);
2363 }
2364
2365 Datum
2366 timestamp_gt_timestamptz(PG_FUNCTION_ARGS)
2367 {
2368 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2369 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2370
2371 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) > 0);
2372 }
2373
2374 Datum
2375 timestamp_le_timestamptz(PG_FUNCTION_ARGS)
2376 {
2377 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2378 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2379
2380 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) <= 0);
2381 }
2382
2383 Datum
2384 timestamp_ge_timestamptz(PG_FUNCTION_ARGS)
2385 {
2386 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2387 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2388
2389 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt2) >= 0);
2390 }
2391
2392 Datum
2393 timestamp_cmp_timestamptz(PG_FUNCTION_ARGS)
2394 {
2395 Timestamp timestampVal = PG_GETARG_TIMESTAMP(0);
2396 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
2397
2398 PG_RETURN_INT32(timestamp_cmp_timestamptz_internal(timestampVal, dt2));
2399 }
2400
2401 Datum
2402 timestamptz_eq_timestamp(PG_FUNCTION_ARGS)
2403 {
2404 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2405 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2406
2407 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) == 0);
2408 }
2409
2410 Datum
2411 timestamptz_ne_timestamp(PG_FUNCTION_ARGS)
2412 {
2413 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2414 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2415
2416 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) != 0);
2417 }
2418
2419 Datum
2420 timestamptz_lt_timestamp(PG_FUNCTION_ARGS)
2421 {
2422 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2423 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2424
2425 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) > 0);
2426 }
2427
2428 Datum
2429 timestamptz_gt_timestamp(PG_FUNCTION_ARGS)
2430 {
2431 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2432 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2433
2434 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) < 0);
2435 }
2436
2437 Datum
2438 timestamptz_le_timestamp(PG_FUNCTION_ARGS)
2439 {
2440 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2441 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2442
2443 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) >= 0);
2444 }
2445
2446 Datum
2447 timestamptz_ge_timestamp(PG_FUNCTION_ARGS)
2448 {
2449 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2450 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2451
2452 PG_RETURN_BOOL(timestamp_cmp_timestamptz_internal(timestampVal, dt1) <= 0);
2453 }
2454
2455 Datum
2456 timestamptz_cmp_timestamp(PG_FUNCTION_ARGS)
2457 {
2458 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
2459 Timestamp timestampVal = PG_GETARG_TIMESTAMP(1);
2460
2461 PG_RETURN_INT32(-timestamp_cmp_timestamptz_internal(timestampVal, dt1));
2462 }
2463
2464
2465 /*
2466 * interval_relop - is interval1 relop interval2
2467 *
2468 * Interval comparison is based on converting interval values to a linear
2469 * representation expressed in the units of the time field (microseconds,
2470 * in the case of integer timestamps) with days assumed to be always 24 hours
2471 * and months assumed to be always 30 days. To avoid overflow, we need a
2472 * wider-than-int64 datatype for the linear representation, so use INT128.
2473 */
2474
2475 static inline INT128
2476 interval_cmp_value(const Interval *interval)
2477 {
2478 INT128 span;
2479 int64 days;
2480
2481 /*
2482 * Combine the month and day fields into an integral number of days.
2483 * Because the inputs are int32, int64 arithmetic suffices here.
2484 */
2485 days = interval->month * INT64CONST(30);
2486 days += interval->day;
2487
2488 /* Widen time field to 128 bits */
2489 span = int64_to_int128(interval->time);
2490
2491 /* Scale up days to microseconds, forming a 128-bit product */
2492 int128_add_int64_mul_int64(&span, days, USECS_PER_DAY);
2493
2494 return span;
2495 }
2496
2497 static int
2498 interval_cmp_internal(const Interval *interval1, const Interval *interval2)
2499 {
2500 INT128 span1 = interval_cmp_value(interval1);
2501 INT128 span2 = interval_cmp_value(interval2);
2502
2503 return int128_compare(span1, span2);
2504 }
2505
2506 static int
2507 interval_sign(const Interval *interval)
2508 {
2509 INT128 span = interval_cmp_value(interval);
2510 INT128 zero = int64_to_int128(0);
2511
2512 return int128_compare(span, zero);
2513 }
2514
2515 Datum
2516 interval_eq(PG_FUNCTION_ARGS)
2517 {
2518 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2519 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2520
2521 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) == 0);
2522 }
2523
2524 Datum
2525 interval_ne(PG_FUNCTION_ARGS)
2526 {
2527 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2528 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2529
2530 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) != 0);
2531 }
2532
2533 Datum
2534 interval_lt(PG_FUNCTION_ARGS)
2535 {
2536 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2537 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2538
2539 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) < 0);
2540 }
2541
2542 Datum
2543 interval_gt(PG_FUNCTION_ARGS)
2544 {
2545 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2546 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2547
2548 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) > 0);
2549 }
2550
2551 Datum
2552 interval_le(PG_FUNCTION_ARGS)
2553 {
2554 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2555 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2556
2557 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) <= 0);
2558 }
2559
2560 Datum
2561 interval_ge(PG_FUNCTION_ARGS)
2562 {
2563 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2564 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2565
2566 PG_RETURN_BOOL(interval_cmp_internal(interval1, interval2) >= 0);
2567 }
2568
2569 Datum
2570 interval_cmp(PG_FUNCTION_ARGS)
2571 {
2572 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
2573 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
2574
2575 PG_RETURN_INT32(interval_cmp_internal(interval1, interval2));
2576 }
2577
2578 /*
2579 * Hashing for intervals
2580 *
2581 * We must produce equal hashvals for values that interval_cmp_internal()
2582 * considers equal. So, compute the net span the same way it does,
2583 * and then hash that.
2584 */
2585 Datum
2586 interval_hash(PG_FUNCTION_ARGS)
2587 {
2588 Interval *interval = PG_GETARG_INTERVAL_P(0);
2589 INT128 span = interval_cmp_value(interval);
2590 int64 span64;
2591
2592 /*
2593 * Use only the least significant 64 bits for hashing. The upper 64 bits
2594 * seldom add any useful information, and besides we must do it like this
2595 * for compatibility with hashes calculated before use of INT128 was
2596 * introduced.
2597 */
2598 span64 = int128_to_int64(span);
2599
2600 return DirectFunctionCall1(hashint8, Int64GetDatumFast(span64));
2601 }
2602
2603 Datum
2604 interval_hash_extended(PG_FUNCTION_ARGS)
2605 {
2606 Interval *interval = PG_GETARG_INTERVAL_P(0);
2607 INT128 span = interval_cmp_value(interval);
2608 int64 span64;
2609
2610 /* Same approach as interval_hash */
2611 span64 = int128_to_int64(span);
2612
2613 return DirectFunctionCall2(hashint8extended, Int64GetDatumFast(span64),
2614 PG_GETARG_DATUM(1));
2615 }
2616
2617 /* overlaps_timestamp() --- implements the SQL OVERLAPS operator.
2618 *
2619 * Algorithm is per SQL spec. This is much harder than you'd think
2620 * because the spec requires us to deliver a non-null answer in some cases
2621 * where some of the inputs are null.
2622 */
2623 Datum
2624 overlaps_timestamp(PG_FUNCTION_ARGS)
2625 {
2626 /*
2627 * The arguments are Timestamps, but we leave them as generic Datums to
2628 * avoid unnecessary conversions between value and reference forms --- not
2629 * to mention possible dereferences of null pointers.
2630 */
2631 Datum ts1 = PG_GETARG_DATUM(0);
2632 Datum te1 = PG_GETARG_DATUM(1);
2633 Datum ts2 = PG_GETARG_DATUM(2);
2634 Datum te2 = PG_GETARG_DATUM(3);
2635 bool ts1IsNull = PG_ARGISNULL(0);
2636 bool te1IsNull = PG_ARGISNULL(1);
2637 bool ts2IsNull = PG_ARGISNULL(2);
2638 bool te2IsNull = PG_ARGISNULL(3);
2639
2640 #define TIMESTAMP_GT(t1,t2) \
2641 DatumGetBool(DirectFunctionCall2(timestamp_gt,t1,t2))
2642 #define TIMESTAMP_LT(t1,t2) \
2643 DatumGetBool(DirectFunctionCall2(timestamp_lt,t1,t2))
2644
2645 /*
2646 * If both endpoints of interval 1 are null, the result is null (unknown).
2647 * If just one endpoint is null, take ts1 as the non-null one. Otherwise,
2648 * take ts1 as the lesser endpoint.
2649 */
2650 if (ts1IsNull)
2651 {
2652 if (te1IsNull)
2653 PG_RETURN_NULL();
2654 /* swap null for non-null */
2655 ts1 = te1;
2656 te1IsNull = true;
2657 }
2658 else if (!te1IsNull)
2659 {
2660 if (TIMESTAMP_GT(ts1, te1))
2661 {
2662 Datum tt = ts1;
2663
2664 ts1 = te1;
2665 te1 = tt;
2666 }
2667 }
2668
2669 /* Likewise for interval 2. */
2670 if (ts2IsNull)
2671 {
2672 if (te2IsNull)
2673 PG_RETURN_NULL();
2674 /* swap null for non-null */
2675 ts2 = te2;
2676 te2IsNull = true;
2677 }
2678 else if (!te2IsNull)
2679 {
2680 if (TIMESTAMP_GT(ts2, te2))
2681 {
2682 Datum tt = ts2;
2683
2684 ts2 = te2;
2685 te2 = tt;
2686 }
2687 }
2688
2689 /*
2690 * At this point neither ts1 nor ts2 is null, so we can consider three
2691 * cases: ts1 > ts2, ts1 < ts2, ts1 = ts2
2692 */
2693 if (TIMESTAMP_GT(ts1, ts2))
2694 {
2695 /*
2696 * This case is ts1 < te2 OR te1 < te2, which may look redundant but
2697 * in the presence of nulls it's not quite completely so.
2698 */
2699 if (te2IsNull)
2700 PG_RETURN_NULL();
2701 if (TIMESTAMP_LT(ts1, te2))
2702 PG_RETURN_BOOL(true);
2703 if (te1IsNull)
2704 PG_RETURN_NULL();
2705
2706 /*
2707 * If te1 is not null then we had ts1 <= te1 above, and we just found
2708 * ts1 >= te2, hence te1 >= te2.
2709 */
2710 PG_RETURN_BOOL(false);
2711 }
2712 else if (TIMESTAMP_LT(ts1, ts2))
2713 {
2714 /* This case is ts2 < te1 OR te2 < te1 */
2715 if (te1IsNull)
2716 PG_RETURN_NULL();
2717 if (TIMESTAMP_LT(ts2, te1))
2718 PG_RETURN_BOOL(true);
2719 if (te2IsNull)
2720 PG_RETURN_NULL();
2721
2722 /*
2723 * If te2 is not null then we had ts2 <= te2 above, and we just found
2724 * ts2 >= te1, hence te2 >= te1.
2725 */
2726 PG_RETURN_BOOL(false);
2727 }
2728 else
2729 {
2730 /*
2731 * For ts1 = ts2 the spec says te1 <> te2 OR te1 = te2, which is a
2732 * rather silly way of saying "true if both are non-null, else null".
2733 */
2734 if (te1IsNull || te2IsNull)
2735 PG_RETURN_NULL();
2736 PG_RETURN_BOOL(true);
2737 }
2738
2739 #undef TIMESTAMP_GT
2740 #undef TIMESTAMP_LT
2741 }
2742
2743
2744 /*----------------------------------------------------------
2745 * "Arithmetic" operators on date/times.
2746 *---------------------------------------------------------*/
2747
2748 Datum
2749 timestamp_smaller(PG_FUNCTION_ARGS)
2750 {
2751 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2752 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2753 Timestamp result;
2754
2755 /* use timestamp_cmp_internal to be sure this agrees with comparisons */
2756 if (timestamp_cmp_internal(dt1, dt2) < 0)
2757 result = dt1;
2758 else
2759 result = dt2;
2760 PG_RETURN_TIMESTAMP(result);
2761 }
2762
2763 Datum
2764 timestamp_larger(PG_FUNCTION_ARGS)
2765 {
2766 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2767 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2768 Timestamp result;
2769
2770 if (timestamp_cmp_internal(dt1, dt2) > 0)
2771 result = dt1;
2772 else
2773 result = dt2;
2774 PG_RETURN_TIMESTAMP(result);
2775 }
2776
2777
2778 Datum
2779 timestamp_mi(PG_FUNCTION_ARGS)
2780 {
2781 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
2782 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
2783 Interval *result;
2784
2785 result = (Interval *) palloc(sizeof(Interval));
2786
2787 /*
2788 * Handle infinities.
2789 *
2790 * We treat anything that amounts to "infinity - infinity" as an error,
2791 * since the interval type has nothing equivalent to NaN.
2792 */
2793 if (TIMESTAMP_NOT_FINITE(dt1) || TIMESTAMP_NOT_FINITE(dt2))
2794 {
2795 if (TIMESTAMP_IS_NOBEGIN(dt1))
2796 {
2797 if (TIMESTAMP_IS_NOBEGIN(dt2))
2798 ereport(ERROR,
2799 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2800 errmsg("interval out of range")));
2801 else
2802 INTERVAL_NOBEGIN(result);
2803 }
2804 else if (TIMESTAMP_IS_NOEND(dt1))
2805 {
2806 if (TIMESTAMP_IS_NOEND(dt2))
2807 ereport(ERROR,
2808 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2809 errmsg("interval out of range")));
2810 else
2811 INTERVAL_NOEND(result);
2812 }
2813 else if (TIMESTAMP_IS_NOBEGIN(dt2))
2814 INTERVAL_NOEND(result);
2815 else /* TIMESTAMP_IS_NOEND(dt2) */
2816 INTERVAL_NOBEGIN(result);
2817
2818 PG_RETURN_INTERVAL_P(result);
2819 }
2820
2821 if (unlikely(pg_sub_s64_overflow(dt1, dt2, &result->time)))
2822 ereport(ERROR,
2823 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2824 errmsg("interval out of range")));
2825
2826 result->month = 0;
2827 result->day = 0;
2828
2829 /*----------
2830 * This is wrong, but removing it breaks a lot of regression tests.
2831 * For example:
2832 *
2833 * test=> SET timezone = 'EST5EDT';
2834 * test=> SELECT
2835 * test-> ('2005-10-30 13:22:00-05'::timestamptz -
2836 * test(> '2005-10-29 13:22:00-04'::timestamptz);
2837 * ?column?
2838 * ----------------
2839 * 1 day 01:00:00
2840 * (1 row)
2841 *
2842 * so adding that to the first timestamp gets:
2843 *
2844 * test=> SELECT
2845 * test-> ('2005-10-29 13:22:00-04'::timestamptz +
2846 * test(> ('2005-10-30 13:22:00-05'::timestamptz -
2847 * test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
2848 * timezone
2849 * --------------------
2850 * 2005-10-30 14:22:00
2851 * (1 row)
2852 *----------
2853 */
2854 result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours,
2855 IntervalPGetDatum(result)));
2856
2857 PG_RETURN_INTERVAL_P(result);
2858 }
2859
2860 /*
2861 * interval_justify_interval()
2862 *
2863 * Adjust interval so 'month', 'day', and 'time' portions are within
2864 * customary bounds. Specifically:
2865 *
2866 * 0 <= abs(time) < 24 hours
2867 * 0 <= abs(day) < 30 days
2868 *
2869 * Also, the sign bit on all three fields is made equal, so either
2870 * all three fields are negative or all are positive.
2871 */
2872 Datum
2873 interval_justify_interval(PG_FUNCTION_ARGS)
2874 {
2875 Interval *span = PG_GETARG_INTERVAL_P(0);
2876 Interval *result;
2877 TimeOffset wholeday;
2878 int32 wholemonth;
2879
2880 result = (Interval *) palloc(sizeof(Interval));
2881 result->month = span->month;
2882 result->day = span->day;
2883 result->time = span->time;
2884
2885 /* do nothing for infinite intervals */
2886 if (INTERVAL_NOT_FINITE(result))
2887 PG_RETURN_INTERVAL_P(result);
2888
2889 /* pre-justify days if it might prevent overflow */
2890 if ((result->day > 0 && result->time > 0) ||
2891 (result->day < 0 && result->time < 0))
2892 {
2893 wholemonth = result->day / DAYS_PER_MONTH;
2894 result->day -= wholemonth * DAYS_PER_MONTH;
2895 if (pg_add_s32_overflow(result->month, wholemonth, &result->month))
2896 ereport(ERROR,
2897 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2898 errmsg("interval out of range")));
2899 }
2900
2901 /*
2902 * Since TimeOffset is int64, abs(wholeday) can't exceed about 1.07e8. If
2903 * we pre-justified then abs(result->day) is less than DAYS_PER_MONTH, so
2904 * this addition can't overflow. If we didn't pre-justify, then day and
2905 * time are of different signs, so it still can't overflow.
2906 */
2907 TMODULO(result->time, wholeday, USECS_PER_DAY);
2908 result->day += wholeday;
2909
2910 wholemonth = result->day / DAYS_PER_MONTH;
2911 result->day -= wholemonth * DAYS_PER_MONTH;
2912 if (pg_add_s32_overflow(result->month, wholemonth, &result->month))
2913 ereport(ERROR,
2914 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2915 errmsg("interval out of range")));
2916
2917 if (result->month > 0 &&
2918 (result->day < 0 || (result->day == 0 && result->time < 0)))
2919 {
2920 result->day += DAYS_PER_MONTH;
2921 result->month--;
2922 }
2923 else if (result->month < 0 &&
2924 (result->day > 0 || (result->day == 0 && result->time > 0)))
2925 {
2926 result->day -= DAYS_PER_MONTH;
2927 result->month++;
2928 }
2929
2930 if (result->day > 0 && result->time < 0)
2931 {
2932 result->time += USECS_PER_DAY;
2933 result->day--;
2934 }
2935 else if (result->day < 0 && result->time > 0)
2936 {
2937 result->time -= USECS_PER_DAY;
2938 result->day++;
2939 }
2940
2941 PG_RETURN_INTERVAL_P(result);
2942 }
2943
2944 /*
2945 * interval_justify_hours()
2946 *
2947 * Adjust interval so 'time' contains less than a whole day, adding
2948 * the excess to 'day'. This is useful for
2949 * situations (such as non-TZ) where '1 day' = '24 hours' is valid,
2950 * e.g. interval subtraction and division.
2951 */
2952 Datum
2953 interval_justify_hours(PG_FUNCTION_ARGS)
2954 {
2955 Interval *span = PG_GETARG_INTERVAL_P(0);
2956 Interval *result;
2957 TimeOffset wholeday;
2958
2959 result = (Interval *) palloc(sizeof(Interval));
2960 result->month = span->month;
2961 result->day = span->day;
2962 result->time = span->time;
2963
2964 /* do nothing for infinite intervals */
2965 if (INTERVAL_NOT_FINITE(result))
2966 PG_RETURN_INTERVAL_P(result);
2967
2968 TMODULO(result->time, wholeday, USECS_PER_DAY);
2969 if (pg_add_s32_overflow(result->day, wholeday, &result->day))
2970 ereport(ERROR,
2971 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
2972 errmsg("interval out of range")));
2973
2974 if (result->day > 0 && result->time < 0)
2975 {
2976 result->time += USECS_PER_DAY;
2977 result->day--;
2978 }
2979 else if (result->day < 0 && result->time > 0)
2980 {
2981 result->time -= USECS_PER_DAY;
2982 result->day++;
2983 }
2984
2985 PG_RETURN_INTERVAL_P(result);
2986 }
2987
2988 /*
2989 * interval_justify_days()
2990 *
2991 * Adjust interval so 'day' contains less than 30 days, adding
2992 * the excess to 'month'.
2993 */
2994 Datum
2995 interval_justify_days(PG_FUNCTION_ARGS)
2996 {
2997 Interval *span = PG_GETARG_INTERVAL_P(0);
2998 Interval *result;
2999 int32 wholemonth;
3000
3001 result = (Interval *) palloc(sizeof(Interval));
3002 result->month = span->month;
3003 result->day = span->day;
3004 result->time = span->time;
3005
3006 /* do nothing for infinite intervals */
3007 if (INTERVAL_NOT_FINITE(result))
3008 PG_RETURN_INTERVAL_P(result);
3009
3010 wholemonth = result->day / DAYS_PER_MONTH;
3011 result->day -= wholemonth * DAYS_PER_MONTH;
3012 if (pg_add_s32_overflow(result->month, wholemonth, &result->month))
3013 ereport(ERROR,
3014 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3015 errmsg("interval out of range")));
3016
3017 if (result->month > 0 && result->day < 0)
3018 {
3019 result->day += DAYS_PER_MONTH;
3020 result->month--;
3021 }
3022 else if (result->month < 0 && result->day > 0)
3023 {
3024 result->day -= DAYS_PER_MONTH;
3025 result->month++;
3026 }
3027
3028 PG_RETURN_INTERVAL_P(result);
3029 }
3030
3031 /* timestamp_pl_interval()
3032 * Add an interval to a timestamp data type.
3033 * Note that interval has provisions for qualitative year/month and day
3034 * units, so try to do the right thing with them.
3035 * To add a month, increment the month, and use the same day of month.
3036 * Then, if the next month has fewer days, set the day of month
3037 * to the last day of month.
3038 * To add a day, increment the mday, and use the same time of day.
3039 * Lastly, add in the "quantitative time".
3040 */
3041 Datum
3042 timestamp_pl_interval(PG_FUNCTION_ARGS)
3043 {
3044 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
3045 Interval *span = PG_GETARG_INTERVAL_P(1);
3046 Timestamp result;
3047
3048 /*
3049 * Handle infinities.
3050 *
3051 * We treat anything that amounts to "infinity - infinity" as an error,
3052 * since the timestamp type has nothing equivalent to NaN.
3053 */
3054 if (INTERVAL_IS_NOBEGIN(span))
3055 {
3056 if (TIMESTAMP_IS_NOEND(timestamp))
3057 ereport(ERROR,
3058 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3059 errmsg("timestamp out of range")));
3060 else
3061 TIMESTAMP_NOBEGIN(result);
3062 }
3063 else if (INTERVAL_IS_NOEND(span))
3064 {
3065 if (TIMESTAMP_IS_NOBEGIN(timestamp))
3066 ereport(ERROR,
3067 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3068 errmsg("timestamp out of range")));
3069 else
3070 TIMESTAMP_NOEND(result);
3071 }
3072 else if (TIMESTAMP_NOT_FINITE(timestamp))
3073 result = timestamp;
3074 else
3075 {
3076 if (span->month != 0)
3077 {
3078 struct pg_tm tt,
3079 *tm = &tt;
3080 fsec_t fsec;
3081
3082 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
3083 ereport(ERROR,
3084 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3085 errmsg("timestamp out of range")));
3086
3087 if (pg_add_s32_overflow(tm->tm_mon, span->month, &tm->tm_mon))
3088 ereport(ERROR,
3089 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3090 errmsg("timestamp out of range")));
3091 if (tm->tm_mon > MONTHS_PER_YEAR)
3092 {
3093 tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
3094 tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
3095 }
3096 else if (tm->tm_mon < 1)
3097 {
3098 tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
3099 tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
3100 }
3101
3102 /* adjust for end of month boundary problems... */
3103 if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
3104 tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
3105
3106 if (tm2timestamp(tm, fsec, NULL, &timestamp) != 0)
3107 ereport(ERROR,
3108 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3109 errmsg("timestamp out of range")));
3110 }
3111
3112 if (span->day != 0)
3113 {
3114 struct pg_tm tt,
3115 *tm = &tt;
3116 fsec_t fsec;
3117 int julian;
3118
3119 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
3120 ereport(ERROR,
3121 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3122 errmsg("timestamp out of range")));
3123
3124 /*
3125 * Add days by converting to and from Julian. We need an overflow
3126 * check here since j2date expects a non-negative integer input.
3127 */
3128 julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
3129 if (pg_add_s32_overflow(julian, span->day, &julian) ||
3130 julian < 0)
3131 ereport(ERROR,
3132 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3133 errmsg("timestamp out of range")));
3134 j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
3135
3136 if (tm2timestamp(tm, fsec, NULL, &timestamp) != 0)
3137 ereport(ERROR,
3138 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3139 errmsg("timestamp out of range")));
3140 }
3141
3142 if (pg_add_s64_overflow(timestamp, span->time, &timestamp))
3143 ereport(ERROR,
3144 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3145 errmsg("timestamp out of range")));
3146
3147 if (!IS_VALID_TIMESTAMP(timestamp))
3148 ereport(ERROR,
3149 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3150 errmsg("timestamp out of range")));
3151
3152 result = timestamp;
3153 }
3154
3155 PG_RETURN_TIMESTAMP(result);
3156 }
3157
3158 Datum
3159 timestamp_mi_interval(PG_FUNCTION_ARGS)
3160 {
3161 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
3162 Interval *span = PG_GETARG_INTERVAL_P(1);
3163 Interval tspan;
3164
3165 interval_um_internal(span, &tspan);
3166
3167 return DirectFunctionCall2(timestamp_pl_interval,
3168 TimestampGetDatum(timestamp),
3169 PointerGetDatum(&tspan));
3170 }
3171
3172
3173 /* timestamptz_pl_interval_internal()
3174 * Add an interval to a timestamptz, in the given (or session) timezone.
3175 *
3176 * Note that interval has provisions for qualitative year/month and day
3177 * units, so try to do the right thing with them.
3178 * To add a month, increment the month, and use the same day of month.
3179 * Then, if the next month has fewer days, set the day of month
3180 * to the last day of month.
3181 * To add a day, increment the mday, and use the same time of day.
3182 * Lastly, add in the "quantitative time".
3183 */
3184 static TimestampTz
3185 timestamptz_pl_interval_internal(TimestampTz timestamp,
3186 Interval *span,
3187 pg_tz *attimezone)
3188 {
3189 TimestampTz result;
3190 int tz;
3191
3192 /*
3193 * Handle infinities.
3194 *
3195 * We treat anything that amounts to "infinity - infinity" as an error,
3196 * since the timestamptz type has nothing equivalent to NaN.
3197 */
3198 if (INTERVAL_IS_NOBEGIN(span))
3199 {
3200 if (TIMESTAMP_IS_NOEND(timestamp))
3201 ereport(ERROR,
3202 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3203 errmsg("timestamp out of range")));
3204 else
3205 TIMESTAMP_NOBEGIN(result);
3206 }
3207 else if (INTERVAL_IS_NOEND(span))
3208 {
3209 if (TIMESTAMP_IS_NOBEGIN(timestamp))
3210 ereport(ERROR,
3211 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3212 errmsg("timestamp out of range")));
3213 else
3214 TIMESTAMP_NOEND(result);
3215 }
3216 else if (TIMESTAMP_NOT_FINITE(timestamp))
3217 result = timestamp;
3218 else
3219 {
3220 /* Use session timezone if caller asks for default */
3221 if (attimezone == NULL)
3222 attimezone = session_timezone;
3223
3224 if (span->month != 0)
3225 {
3226 struct pg_tm tt,
3227 *tm = &tt;
3228 fsec_t fsec;
3229
3230 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, attimezone) != 0)
3231 ereport(ERROR,
3232 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3233 errmsg("timestamp out of range")));
3234
3235 if (pg_add_s32_overflow(tm->tm_mon, span->month, &tm->tm_mon))
3236 ereport(ERROR,
3237 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3238 errmsg("timestamp out of range")));
3239 if (tm->tm_mon > MONTHS_PER_YEAR)
3240 {
3241 tm->tm_year += (tm->tm_mon - 1) / MONTHS_PER_YEAR;
3242 tm->tm_mon = ((tm->tm_mon - 1) % MONTHS_PER_YEAR) + 1;
3243 }
3244 else if (tm->tm_mon < 1)
3245 {
3246 tm->tm_year += tm->tm_mon / MONTHS_PER_YEAR - 1;
3247 tm->tm_mon = tm->tm_mon % MONTHS_PER_YEAR + MONTHS_PER_YEAR;
3248 }
3249
3250 /* adjust for end of month boundary problems... */
3251 if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
3252 tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);
3253
3254 tz = DetermineTimeZoneOffset(tm, attimezone);
3255
3256 if (tm2timestamp(tm, fsec, &tz, &timestamp) != 0)
3257 ereport(ERROR,
3258 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3259 errmsg("timestamp out of range")));
3260 }
3261
3262 if (span->day != 0)
3263 {
3264 struct pg_tm tt,
3265 *tm = &tt;
3266 fsec_t fsec;
3267 int julian;
3268
3269 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, attimezone) != 0)
3270 ereport(ERROR,
3271 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3272 errmsg("timestamp out of range")));
3273
3274 /*
3275 * Add days by converting to and from Julian. We need an overflow
3276 * check here since j2date expects a non-negative integer input.
3277 * In practice though, it will give correct answers for small
3278 * negative Julian dates; we should allow -1 to avoid
3279 * timezone-dependent failures, as discussed in timestamp.h.
3280 */
3281 julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday);
3282 if (pg_add_s32_overflow(julian, span->day, &julian) ||
3283 julian < -1)
3284 ereport(ERROR,
3285 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3286 errmsg("timestamp out of range")));
3287 j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
3288
3289 tz = DetermineTimeZoneOffset(tm, attimezone);
3290
3291 if (tm2timestamp(tm, fsec, &tz, &timestamp) != 0)
3292 ereport(ERROR,
3293 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3294 errmsg("timestamp out of range")));
3295 }
3296
3297 if (pg_add_s64_overflow(timestamp, span->time, &timestamp))
3298 ereport(ERROR,
3299 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3300 errmsg("timestamp out of range")));
3301
3302 if (!IS_VALID_TIMESTAMP(timestamp))
3303 ereport(ERROR,
3304 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3305 errmsg("timestamp out of range")));
3306
3307 result = timestamp;
3308 }
3309
3310 return result;
3311 }
3312
3313 /* timestamptz_mi_interval_internal()
3314 * As above, but subtract the interval.
3315 */
3316 static TimestampTz
3317 timestamptz_mi_interval_internal(TimestampTz timestamp,
3318 Interval *span,
3319 pg_tz *attimezone)
3320 {
3321 Interval tspan;
3322
3323 interval_um_internal(span, &tspan);
3324
3325 return timestamptz_pl_interval_internal(timestamp, &tspan, attimezone);
3326 }
3327
3328 /* timestamptz_pl_interval()
3329 * Add an interval to a timestamptz, in the session timezone.
3330 */
3331 Datum
3332 timestamptz_pl_interval(PG_FUNCTION_ARGS)
3333 {
3334 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
3335 Interval *span = PG_GETARG_INTERVAL_P(1);
3336
3337 PG_RETURN_TIMESTAMP(timestamptz_pl_interval_internal(timestamp, span, NULL));
3338 }
3339
3340 Datum
3341 timestamptz_mi_interval(PG_FUNCTION_ARGS)
3342 {
3343 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
3344 Interval *span = PG_GETARG_INTERVAL_P(1);
3345
3346 PG_RETURN_TIMESTAMP(timestamptz_mi_interval_internal(timestamp, span, NULL));
3347 }
3348
3349 /* timestamptz_pl_interval_at_zone()
3350 * Add an interval to a timestamptz, in the specified timezone.
3351 */
3352 Datum
3353 timestamptz_pl_interval_at_zone(PG_FUNCTION_ARGS)
3354 {
3355 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
3356 Interval *span = PG_GETARG_INTERVAL_P(1);
3357 text *zone = PG_GETARG_TEXT_PP(2);
3358 pg_tz *attimezone = lookup_timezone(zone);
3359
3360 PG_RETURN_TIMESTAMP(timestamptz_pl_interval_internal(timestamp, span, attimezone));
3361 }
3362
3363 Datum
3364 timestamptz_mi_interval_at_zone(PG_FUNCTION_ARGS)
3365 {
3366 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
3367 Interval *span = PG_GETARG_INTERVAL_P(1);
3368 text *zone = PG_GETARG_TEXT_PP(2);
3369 pg_tz *attimezone = lookup_timezone(zone);
3370
3371 PG_RETURN_TIMESTAMP(timestamptz_mi_interval_internal(timestamp, span, attimezone));
3372 }
3373
3374 /* interval_um_internal()
3375 * Negate an interval.
3376 */
3377 static void
3378 interval_um_internal(const Interval *interval, Interval *result)
3379 {
3380 if (INTERVAL_IS_NOBEGIN(interval))
3381 INTERVAL_NOEND(result);
3382 else if (INTERVAL_IS_NOEND(interval))
3383 INTERVAL_NOBEGIN(result);
3384 else
3385 {
3386 /* Negate each field, guarding against overflow */
3387 if (pg_sub_s64_overflow(INT64CONST(0), interval->time, &result->time) ||
3388 pg_sub_s32_overflow(0, interval->day, &result->day) ||
3389 pg_sub_s32_overflow(0, interval->month, &result->month) ||
3390 INTERVAL_NOT_FINITE(result))
3391 ereport(ERROR,
3392 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3393 errmsg("interval out of range")));
3394 }
3395 }
3396
3397 Datum
3398 interval_um(PG_FUNCTION_ARGS)
3399 {
3400 Interval *interval = PG_GETARG_INTERVAL_P(0);
3401 Interval *result;
3402
3403 result = (Interval *) palloc(sizeof(Interval));
3404 interval_um_internal(interval, result);
3405
3406 PG_RETURN_INTERVAL_P(result);
3407 }
3408
3409
3410 Datum
3411 interval_smaller(PG_FUNCTION_ARGS)
3412 {
3413 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
3414 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
3415 Interval *result;
3416
3417 /* use interval_cmp_internal to be sure this agrees with comparisons */
3418 if (interval_cmp_internal(interval1, interval2) < 0)
3419 result = interval1;
3420 else
3421 result = interval2;
3422 PG_RETURN_INTERVAL_P(result);
3423 }
3424
3425 Datum
3426 interval_larger(PG_FUNCTION_ARGS)
3427 {
3428 Interval *interval1 = PG_GETARG_INTERVAL_P(0);
3429 Interval *interval2 = PG_GETARG_INTERVAL_P(1);
3430 Interval *result;
3431
3432 if (interval_cmp_internal(interval1, interval2) > 0)
3433 result = interval1;
3434 else
3435 result = interval2;
3436 PG_RETURN_INTERVAL_P(result);
3437 }
3438
3439 static void
3440 finite_interval_pl(const Interval *span1, const Interval *span2, Interval *result)
3441 {
3442 Assert(!INTERVAL_NOT_FINITE(span1));
3443 Assert(!INTERVAL_NOT_FINITE(span2));
3444
3445 if (pg_add_s32_overflow(span1->month, span2->month, &result->month) ||
3446 pg_add_s32_overflow(span1->day, span2->day, &result->day) ||
3447 pg_add_s64_overflow(span1->time, span2->time, &result->time) ||
3448 INTERVAL_NOT_FINITE(result))
3449 ereport(ERROR,
3450 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3451 errmsg("interval out of range")));
3452 }
3453
3454 Datum
3455 interval_pl(PG_FUNCTION_ARGS)
3456 {
3457 Interval *span1 = PG_GETARG_INTERVAL_P(0);
3458 Interval *span2 = PG_GETARG_INTERVAL_P(1);
3459 Interval *result;
3460
3461 result = (Interval *) palloc(sizeof(Interval));
3462
3463 /*
3464 * Handle infinities.
3465 *
3466 * We treat anything that amounts to "infinity - infinity" as an error,
3467 * since the interval type has nothing equivalent to NaN.
3468 */
3469 if (INTERVAL_IS_NOBEGIN(span1))
3470 {
3471 if (INTERVAL_IS_NOEND(span2))
3472 ereport(ERROR,
3473 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3474 errmsg("interval out of range")));
3475 else
3476 INTERVAL_NOBEGIN(result);
3477 }
3478 else if (INTERVAL_IS_NOEND(span1))
3479 {
3480 if (INTERVAL_IS_NOBEGIN(span2))
3481 ereport(ERROR,
3482 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3483 errmsg("interval out of range")));
3484 else
3485 INTERVAL_NOEND(result);
3486 }
3487 else if (INTERVAL_NOT_FINITE(span2))
3488 memcpy(result, span2, sizeof(Interval));
3489 else
3490 finite_interval_pl(span1, span2, result);
3491
3492 PG_RETURN_INTERVAL_P(result);
3493 }
3494
3495 static void
3496 finite_interval_mi(const Interval *span1, const Interval *span2, Interval *result)
3497 {
3498 Assert(!INTERVAL_NOT_FINITE(span1));
3499 Assert(!INTERVAL_NOT_FINITE(span2));
3500
3501 if (pg_sub_s32_overflow(span1->month, span2->month, &result->month) ||
3502 pg_sub_s32_overflow(span1->day, span2->day, &result->day) ||
3503 pg_sub_s64_overflow(span1->time, span2->time, &result->time) ||
3504 INTERVAL_NOT_FINITE(result))
3505 ereport(ERROR,
3506 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3507 errmsg("interval out of range")));
3508 }
3509
3510 Datum
3511 interval_mi(PG_FUNCTION_ARGS)
3512 {
3513 Interval *span1 = PG_GETARG_INTERVAL_P(0);
3514 Interval *span2 = PG_GETARG_INTERVAL_P(1);
3515 Interval *result;
3516
3517 result = (Interval *) palloc(sizeof(Interval));
3518
3519 /*
3520 * Handle infinities.
3521 *
3522 * We treat anything that amounts to "infinity - infinity" as an error,
3523 * since the interval type has nothing equivalent to NaN.
3524 */
3525 if (INTERVAL_IS_NOBEGIN(span1))
3526 {
3527 if (INTERVAL_IS_NOBEGIN(span2))
3528 ereport(ERROR,
3529 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3530 errmsg("interval out of range")));
3531 else
3532 INTERVAL_NOBEGIN(result);
3533 }
3534 else if (INTERVAL_IS_NOEND(span1))
3535 {
3536 if (INTERVAL_IS_NOEND(span2))
3537 ereport(ERROR,
3538 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3539 errmsg("interval out of range")));
3540 else
3541 INTERVAL_NOEND(result);
3542 }
3543 else if (INTERVAL_IS_NOBEGIN(span2))
3544 INTERVAL_NOEND(result);
3545 else if (INTERVAL_IS_NOEND(span2))
3546 INTERVAL_NOBEGIN(result);
3547 else
3548 finite_interval_mi(span1, span2, result);
3549
3550 PG_RETURN_INTERVAL_P(result);
3551 }
3552
3553 /*
3554 * There is no interval_abs(): it is unclear what value to return:
3555 * http://archives.postgresql.org/pgsql-general/2009-10/msg01031.php
3556 * http://archives.postgresql.org/pgsql-general/2009-11/msg00041.php
3557 */
3558
3559 Datum
3560 interval_mul(PG_FUNCTION_ARGS)
3561 {
3562 Interval *span = PG_GETARG_INTERVAL_P(0);
3563 float8 factor = PG_GETARG_FLOAT8(1);
3564 double month_remainder_days,
3565 sec_remainder,
3566 result_double;
3567 int32 orig_month = span->month,
3568 orig_day = span->day;
3569 Interval *result;
3570
3571 result = (Interval *) palloc(sizeof(Interval));
3572
3573 /*
3574 * Handle NaN and infinities.
3575 *
3576 * We treat "0 * infinity" and "infinity * 0" as errors, since the
3577 * interval type has nothing equivalent to NaN.
3578 */
3579 if (isnan(factor))
3580 goto out_of_range;
3581
3582 if (INTERVAL_NOT_FINITE(span))
3583 {
3584 if (factor == 0.0)
3585 goto out_of_range;
3586
3587 if (factor < 0.0)
3588 interval_um_internal(span, result);
3589 else
3590 memcpy(result, span, sizeof(Interval));
3591
3592 PG_RETURN_INTERVAL_P(result);
3593 }
3594 if (isinf(factor))
3595 {
3596 int isign = interval_sign(span);
3597
3598 if (isign == 0)
3599 goto out_of_range;
3600
3601 if (factor * isign < 0)
3602 INTERVAL_NOBEGIN(result);
3603 else
3604 INTERVAL_NOEND(result);
3605
3606 PG_RETURN_INTERVAL_P(result);
3607 }
3608
3609 result_double = span->month * factor;
3610 if (isnan(result_double) || !FLOAT8_FITS_IN_INT32(result_double))
3611 goto out_of_range;
3612 result->month = (int32) result_double;
3613
3614 result_double = span->day * factor;
3615 if (isnan(result_double) || !FLOAT8_FITS_IN_INT32(result_double))
3616 goto out_of_range;
3617 result->day = (int32) result_double;
3618
3619 /*
3620 * The above correctly handles the whole-number part of the month and day
3621 * products, but we have to do something with any fractional part
3622 * resulting when the factor is non-integral. We cascade the fractions
3623 * down to lower units using the conversion factors DAYS_PER_MONTH and
3624 * SECS_PER_DAY. Note we do NOT cascade up, since we are not forced to do
3625 * so by the representation. The user can choose to cascade up later,
3626 * using justify_hours and/or justify_days.
3627 */
3628
3629 /*
3630 * Fractional months full days into days.
3631 *
3632 * Floating point calculation are inherently imprecise, so these
3633 * calculations are crafted to produce the most reliable result possible.
3634 * TSROUND() is needed to more accurately produce whole numbers where
3635 * appropriate.
3636 */
3637 month_remainder_days = (orig_month * factor - result->month) * DAYS_PER_MONTH;
3638 month_remainder_days = TSROUND(month_remainder_days);
3639 sec_remainder = (orig_day * factor - result->day +
3640 month_remainder_days - (int) month_remainder_days) * SECS_PER_DAY;
3641 sec_remainder = TSROUND(sec_remainder);
3642
3643 /*
3644 * Might have 24:00:00 hours due to rounding, or >24 hours because of time
3645 * cascade from months and days. It might still be >24 if the combination
3646 * of cascade and the seconds factor operation itself.
3647 */
3648 if (fabs(sec_remainder) >= SECS_PER_DAY)
3649 {
3650 if (pg_add_s32_overflow(result->day,
3651 (int) (sec_remainder / SECS_PER_DAY),
3652 &result->day))
3653 goto out_of_range;
3654 sec_remainder -= (int) (sec_remainder / SECS_PER_DAY) * SECS_PER_DAY;
3655 }
3656
3657 /* cascade units down */
3658 if (pg_add_s32_overflow(result->day, (int32) month_remainder_days,
3659 &result->day))
3660 goto out_of_range;
3661 result_double = rint(span->time * factor + sec_remainder * USECS_PER_SEC);
3662 if (isnan(result_double) || !FLOAT8_FITS_IN_INT64(result_double))
3663 goto out_of_range;
3664 result->time = (int64) result_double;
3665
3666 if (INTERVAL_NOT_FINITE(result))
3667 goto out_of_range;
3668
3669 PG_RETURN_INTERVAL_P(result);
3670
3671 out_of_range:
3672 ereport(ERROR,
3673 errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3674 errmsg("interval out of range"));
3675
3676 PG_RETURN_NULL(); /* keep compiler quiet */
3677 }
3678
3679 Datum
3680 mul_d_interval(PG_FUNCTION_ARGS)
3681 {
3682 /* Args are float8 and Interval *, but leave them as generic Datum */
3683 Datum factor = PG_GETARG_DATUM(0);
3684 Datum span = PG_GETARG_DATUM(1);
3685
3686 return DirectFunctionCall2(interval_mul, span, factor);
3687 }
3688
3689 Datum
3690 interval_div(PG_FUNCTION_ARGS)
3691 {
3692 Interval *span = PG_GETARG_INTERVAL_P(0);
3693 float8 factor = PG_GETARG_FLOAT8(1);
3694 double month_remainder_days,
3695 sec_remainder,
3696 result_double;
3697 int32 orig_month = span->month,
3698 orig_day = span->day;
3699 Interval *result;
3700
3701 result = (Interval *) palloc(sizeof(Interval));
3702
3703 if (factor == 0.0)
3704 ereport(ERROR,
3705 (errcode(ERRCODE_DIVISION_BY_ZERO),
3706 errmsg("division by zero")));
3707
3708 /*
3709 * Handle NaN and infinities.
3710 *
3711 * We treat "infinity / infinity" as an error, since the interval type has
3712 * nothing equivalent to NaN. Otherwise, dividing by infinity is handled
3713 * by the regular division code, causing all fields to be set to zero.
3714 */
3715 if (isnan(factor))
3716 goto out_of_range;
3717
3718 if (INTERVAL_NOT_FINITE(span))
3719 {
3720 if (isinf(factor))
3721 goto out_of_range;
3722
3723 if (factor < 0.0)
3724 interval_um_internal(span, result);
3725 else
3726 memcpy(result, span, sizeof(Interval));
3727
3728 PG_RETURN_INTERVAL_P(result);
3729 }
3730
3731 result_double = span->month / factor;
3732 if (isnan(result_double) || !FLOAT8_FITS_IN_INT32(result_double))
3733 goto out_of_range;
3734 result->month = (int32) result_double;
3735
3736 result_double = span->day / factor;
3737 if (isnan(result_double) || !FLOAT8_FITS_IN_INT32(result_double))
3738 goto out_of_range;
3739 result->day = (int32) result_double;
3740
3741 /*
3742 * Fractional months full days into days. See comment in interval_mul().
3743 */
3744 month_remainder_days = (orig_month / factor - result->month) * DAYS_PER_MONTH;
3745 month_remainder_days = TSROUND(month_remainder_days);
3746 sec_remainder = (orig_day / factor - result->day +
3747 month_remainder_days - (int) month_remainder_days) * SECS_PER_DAY;
3748 sec_remainder = TSROUND(sec_remainder);
3749 if (fabs(sec_remainder) >= SECS_PER_DAY)
3750 {
3751 if (pg_add_s32_overflow(result->day,
3752 (int) (sec_remainder / SECS_PER_DAY),
3753 &result->day))
3754 goto out_of_range;
3755 sec_remainder -= (int) (sec_remainder / SECS_PER_DAY) * SECS_PER_DAY;
3756 }
3757
3758 /* cascade units down */
3759 if (pg_add_s32_overflow(result->day, (int32) month_remainder_days,
3760 &result->day))
3761 goto out_of_range;
3762 result_double = rint(span->time / factor + sec_remainder * USECS_PER_SEC);
3763 if (isnan(result_double) || !FLOAT8_FITS_IN_INT64(result_double))
3764 goto out_of_range;
3765 result->time = (int64) result_double;
3766
3767 if (INTERVAL_NOT_FINITE(result))
3768 goto out_of_range;
3769
3770 PG_RETURN_INTERVAL_P(result);
3771
3772 out_of_range:
3773 ereport(ERROR,
3774 errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3775 errmsg("interval out of range"));
3776
3777 PG_RETURN_NULL(); /* keep compiler quiet */
3778 }
3779
3780
3781 /*
3782 * in_range support functions for timestamps and intervals.
3783 *
3784 * Per SQL spec, we support these with interval as the offset type.
3785 * The spec's restriction that the offset not be negative is a bit hard to
3786 * decipher for intervals, but we choose to interpret it the same as our
3787 * interval comparison operators would.
3788 */
3789
3790 Datum
3791 in_range_timestamptz_interval(PG_FUNCTION_ARGS)
3792 {
3793 TimestampTz val = PG_GETARG_TIMESTAMPTZ(0);
3794 TimestampTz base = PG_GETARG_TIMESTAMPTZ(1);
3795 Interval *offset = PG_GETARG_INTERVAL_P(2);
3796 bool sub = PG_GETARG_BOOL(3);
3797 bool less = PG_GETARG_BOOL(4);
3798 TimestampTz sum;
3799
3800 if (interval_sign(offset) < 0)
3801 ereport(ERROR,
3802 (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3803 errmsg("invalid preceding or following size in window function")));
3804
3805 /*
3806 * Deal with cases where both base and offset are infinite, and computing
3807 * base +/- offset would cause an error. As for float and numeric types,
3808 * we assume that all values infinitely precede +infinity and infinitely
3809 * follow -infinity. See in_range_float8_float8() for reasoning.
3810 */
3811 if (INTERVAL_IS_NOEND(offset) &&
3812 (sub ? TIMESTAMP_IS_NOEND(base) : TIMESTAMP_IS_NOBEGIN(base)))
3813 PG_RETURN_BOOL(true);
3814
3815 /* We don't currently bother to avoid overflow hazards here */
3816 if (sub)
3817 sum = timestamptz_mi_interval_internal(base, offset, NULL);
3818 else
3819 sum = timestamptz_pl_interval_internal(base, offset, NULL);
3820
3821 if (less)
3822 PG_RETURN_BOOL(val <= sum);
3823 else
3824 PG_RETURN_BOOL(val >= sum);
3825 }
3826
3827 Datum
3828 in_range_timestamp_interval(PG_FUNCTION_ARGS)
3829 {
3830 Timestamp val = PG_GETARG_TIMESTAMP(0);
3831 Timestamp base = PG_GETARG_TIMESTAMP(1);
3832 Interval *offset = PG_GETARG_INTERVAL_P(2);
3833 bool sub = PG_GETARG_BOOL(3);
3834 bool less = PG_GETARG_BOOL(4);
3835 Timestamp sum;
3836
3837 if (interval_sign(offset) < 0)
3838 ereport(ERROR,
3839 (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3840 errmsg("invalid preceding or following size in window function")));
3841
3842 /*
3843 * Deal with cases where both base and offset are infinite, and computing
3844 * base +/- offset would cause an error. As for float and numeric types,
3845 * we assume that all values infinitely precede +infinity and infinitely
3846 * follow -infinity. See in_range_float8_float8() for reasoning.
3847 */
3848 if (INTERVAL_IS_NOEND(offset) &&
3849 (sub ? TIMESTAMP_IS_NOEND(base) : TIMESTAMP_IS_NOBEGIN(base)))
3850 PG_RETURN_BOOL(true);
3851
3852 /* We don't currently bother to avoid overflow hazards here */
3853 if (sub)
3854 sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_mi_interval,
3855 TimestampGetDatum(base),
3856 IntervalPGetDatum(offset)));
3857 else
3858 sum = DatumGetTimestamp(DirectFunctionCall2(timestamp_pl_interval,
3859 TimestampGetDatum(base),
3860 IntervalPGetDatum(offset)));
3861
3862 if (less)
3863 PG_RETURN_BOOL(val <= sum);
3864 else
3865 PG_RETURN_BOOL(val >= sum);
3866 }
3867
3868 Datum
3869 in_range_interval_interval(PG_FUNCTION_ARGS)
3870 {
3871 Interval *val = PG_GETARG_INTERVAL_P(0);
3872 Interval *base = PG_GETARG_INTERVAL_P(1);
3873 Interval *offset = PG_GETARG_INTERVAL_P(2);
3874 bool sub = PG_GETARG_BOOL(3);
3875 bool less = PG_GETARG_BOOL(4);
3876 Interval *sum;
3877
3878 if (interval_sign(offset) < 0)
3879 ereport(ERROR,
3880 (errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
3881 errmsg("invalid preceding or following size in window function")));
3882
3883 /*
3884 * Deal with cases where both base and offset are infinite, and computing
3885 * base +/- offset would cause an error. As for float and numeric types,
3886 * we assume that all values infinitely precede +infinity and infinitely
3887 * follow -infinity. See in_range_float8_float8() for reasoning.
3888 */
3889 if (INTERVAL_IS_NOEND(offset) &&
3890 (sub ? INTERVAL_IS_NOEND(base) : INTERVAL_IS_NOBEGIN(base)))
3891 PG_RETURN_BOOL(true);
3892
3893 /* We don't currently bother to avoid overflow hazards here */
3894 if (sub)
3895 sum = DatumGetIntervalP(DirectFunctionCall2(interval_mi,
3896 IntervalPGetDatum(base),
3897 IntervalPGetDatum(offset)));
3898 else
3899 sum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3900 IntervalPGetDatum(base),
3901 IntervalPGetDatum(offset)));
3902
3903 if (less)
3904 PG_RETURN_BOOL(interval_cmp_internal(val, sum) <= 0);
3905 else
3906 PG_RETURN_BOOL(interval_cmp_internal(val, sum) >= 0);
3907 }
3908
3909
3910 /*
3911 * Prepare state data for an interval aggregate function, that needs to compute
3912 * sum and count, in the aggregate's memory context.
3913 *
3914 * The function is used when the state data needs to be allocated in aggregate's
3915 * context. When the state data needs to be allocated in the current memory
3916 * context, we use palloc0 directly e.g. interval_avg_deserialize().
3917 */
3918 static IntervalAggState *
3919 makeIntervalAggState(FunctionCallInfo fcinfo)
3920 {
3921 IntervalAggState *state;
3922 MemoryContext agg_context;
3923 MemoryContext old_context;
3924
3925 if (!AggCheckCallContext(fcinfo, &agg_context))
3926 elog(ERROR, "aggregate function called in non-aggregate context");
3927
3928 old_context = MemoryContextSwitchTo(agg_context);
3929
3930 state = (IntervalAggState *) palloc0(sizeof(IntervalAggState));
3931
3932 MemoryContextSwitchTo(old_context);
3933
3934 return state;
3935 }
3936
3937 /*
3938 * Accumulate a new input value for interval aggregate functions.
3939 */
3940 static void
3941 do_interval_accum(IntervalAggState *state, Interval *newval)
3942 {
3943 /* Infinite inputs are counted separately, and do not affect "N" */
3944 if (INTERVAL_IS_NOBEGIN(newval))
3945 {
3946 state->nInfcount++;
3947 return;
3948 }
3949
3950 if (INTERVAL_IS_NOEND(newval))
3951 {
3952 state->pInfcount++;
3953 return;
3954 }
3955
3956 finite_interval_pl(&state->sumX, newval, &state->sumX);
3957 state->N++;
3958 }
3959
3960 /*
3961 * Remove the given interval value from the aggregated state.
3962 */
3963 static void
3964 do_interval_discard(IntervalAggState *state, Interval *newval)
3965 {
3966 /* Infinite inputs are counted separately, and do not affect "N" */
3967 if (INTERVAL_IS_NOBEGIN(newval))
3968 {
3969 state->nInfcount--;
3970 return;
3971 }
3972
3973 if (INTERVAL_IS_NOEND(newval))
3974 {
3975 state->pInfcount--;
3976 return;
3977 }
3978
3979 /* Handle the to-be-discarded finite value. */
3980 state->N--;
3981 if (state->N > 0)
3982 finite_interval_mi(&state->sumX, newval, &state->sumX);
3983 else
3984 {
3985 /* All values discarded, reset the state */
3986 Assert(state->N == 0);
3987 memset(&state->sumX, 0, sizeof(state->sumX));
3988 }
3989 }
3990
3991 /*
3992 * Transition function for sum() and avg() interval aggregates.
3993 */
3994 Datum
3995 interval_avg_accum(PG_FUNCTION_ARGS)
3996 {
3997 IntervalAggState *state;
3998
3999 state = PG_ARGISNULL(0) ? NULL : (IntervalAggState *) PG_GETARG_POINTER(0);
4000
4001 /* Create the state data on the first call */
4002 if (state == NULL)
4003 state = makeIntervalAggState(fcinfo);
4004
4005 if (!PG_ARGISNULL(1))
4006 do_interval_accum(state, PG_GETARG_INTERVAL_P(1));
4007
4008 PG_RETURN_POINTER(state);
4009 }
4010
4011 /*
4012 * Combine function for sum() and avg() interval aggregates.
4013 *
4014 * Combine the given internal aggregate states and place the combination in
4015 * the first argument.
4016 */
4017 Datum
4018 interval_avg_combine(PG_FUNCTION_ARGS)
4019 {
4020 IntervalAggState *state1;
4021 IntervalAggState *state2;
4022
4023 state1 = PG_ARGISNULL(0) ? NULL : (IntervalAggState *) PG_GETARG_POINTER(0);
4024 state2 = PG_ARGISNULL(1) ? NULL : (IntervalAggState *) PG_GETARG_POINTER(1);
4025
4026 if (state2 == NULL)
4027 PG_RETURN_POINTER(state1);
4028
4029 if (state1 == NULL)
4030 {
4031 /* manually copy all fields from state2 to state1 */
4032 state1 = makeIntervalAggState(fcinfo);
4033
4034 state1->N = state2->N;
4035 state1->pInfcount = state2->pInfcount;
4036 state1->nInfcount = state2->nInfcount;
4037
4038 state1->sumX.day = state2->sumX.day;
4039 state1->sumX.month = state2->sumX.month;
4040 state1->sumX.time = state2->sumX.time;
4041
4042 PG_RETURN_POINTER(state1);
4043 }
4044
4045 state1->N += state2->N;
4046 state1->pInfcount += state2->pInfcount;
4047 state1->nInfcount += state2->nInfcount;
4048
4049 /* Accumulate finite interval values, if any. */
4050 if (state2->N > 0)
4051 finite_interval_pl(&state1->sumX, &state2->sumX, &state1->sumX);
4052
4053 PG_RETURN_POINTER(state1);
4054 }
4055
4056 /*
4057 * interval_avg_serialize
4058 * Serialize IntervalAggState for interval aggregates.
4059 */
4060 Datum
4061 interval_avg_serialize(PG_FUNCTION_ARGS)
4062 {
4063 IntervalAggState *state;
4064 StringInfoData buf;
4065 bytea *result;
4066
4067 /* Ensure we disallow calling when not in aggregate context */
4068 if (!AggCheckCallContext(fcinfo, NULL))
4069 elog(ERROR, "aggregate function called in non-aggregate context");
4070
4071 state = (IntervalAggState *) PG_GETARG_POINTER(0);
4072
4073 pq_begintypsend(&buf);
4074
4075 /* N */
4076 pq_sendint64(&buf, state->N);
4077
4078 /* sumX */
4079 pq_sendint64(&buf, state->sumX.time);
4080 pq_sendint32(&buf, state->sumX.day);
4081 pq_sendint32(&buf, state->sumX.month);
4082
4083 /* pInfcount */
4084 pq_sendint64(&buf, state->pInfcount);
4085
4086 /* nInfcount */
4087 pq_sendint64(&buf, state->nInfcount);
4088
4089 result = pq_endtypsend(&buf);
4090
4091 PG_RETURN_BYTEA_P(result);
4092 }
4093
4094 /*
4095 * interval_avg_deserialize
4096 * Deserialize bytea into IntervalAggState for interval aggregates.
4097 */
4098 Datum
4099 interval_avg_deserialize(PG_FUNCTION_ARGS)
4100 {
4101 bytea *sstate;
4102 IntervalAggState *result;
4103 StringInfoData buf;
4104
4105 if (!AggCheckCallContext(fcinfo, NULL))
4106 elog(ERROR, "aggregate function called in non-aggregate context");
4107
4108 sstate = PG_GETARG_BYTEA_PP(0);
4109
4110 /*
4111 * Initialize a StringInfo so that we can "receive" it using the standard
4112 * recv-function infrastructure.
4113 */
4114 initReadOnlyStringInfo(&buf, VARDATA_ANY(sstate),
4115 VARSIZE_ANY_EXHDR(sstate));
4116
4117 result = (IntervalAggState *) palloc0(sizeof(IntervalAggState));
4118
4119 /* N */
4120 result->N = pq_getmsgint64(&buf);
4121
4122 /* sumX */
4123 result->sumX.time = pq_getmsgint64(&buf);
4124 result->sumX.day = pq_getmsgint(&buf, 4);
4125 result->sumX.month = pq_getmsgint(&buf, 4);
4126
4127 /* pInfcount */
4128 result->pInfcount = pq_getmsgint64(&buf);
4129
4130 /* nInfcount */
4131 result->nInfcount = pq_getmsgint64(&buf);
4132
4133 pq_getmsgend(&buf);
4134
4135 PG_RETURN_POINTER(result);
4136 }
4137
4138 /*
4139 * Inverse transition function for sum() and avg() interval aggregates.
4140 */
4141 Datum
4142 interval_avg_accum_inv(PG_FUNCTION_ARGS)
4143 {
4144 IntervalAggState *state;
4145
4146 state = PG_ARGISNULL(0) ? NULL : (IntervalAggState *) PG_GETARG_POINTER(0);
4147
4148 /* Should not get here with no state */
4149 if (state == NULL)
4150 elog(ERROR, "interval_avg_accum_inv called with NULL state");
4151
4152 if (!PG_ARGISNULL(1))
4153 do_interval_discard(state, PG_GETARG_INTERVAL_P(1));
4154
4155 PG_RETURN_POINTER(state);
4156 }
4157
4158 /* avg(interval) aggregate final function */
4159 Datum
4160 interval_avg(PG_FUNCTION_ARGS)
4161 {
4162 IntervalAggState *state;
4163
4164 state = PG_ARGISNULL(0) ? NULL : (IntervalAggState *) PG_GETARG_POINTER(0);
4165
4166 /* If there were no non-null inputs, return NULL */
4167 if (state == NULL || IA_TOTAL_COUNT(state) == 0)
4168 PG_RETURN_NULL();
4169
4170 /*
4171 * Aggregating infinities that all have the same sign produces infinity
4172 * with that sign. Aggregating infinities with different signs results in
4173 * an error.
4174 */
4175 if (state->pInfcount > 0 || state->nInfcount > 0)
4176 {
4177 Interval *result;
4178
4179 if (state->pInfcount > 0 && state->nInfcount > 0)
4180 ereport(ERROR,
4181 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4182 errmsg("interval out of range")));
4183
4184 result = (Interval *) palloc(sizeof(Interval));
4185 if (state->pInfcount > 0)
4186 INTERVAL_NOEND(result);
4187 else
4188 INTERVAL_NOBEGIN(result);
4189
4190 PG_RETURN_INTERVAL_P(result);
4191 }
4192
4193 return DirectFunctionCall2(interval_div,
4194 IntervalPGetDatum(&state->sumX),
4195 Float8GetDatum((double) state->N));
4196 }
4197
4198 /* sum(interval) aggregate final function */
4199 Datum
4200 interval_sum(PG_FUNCTION_ARGS)
4201 {
4202 IntervalAggState *state;
4203 Interval *result;
4204
4205 state = PG_ARGISNULL(0) ? NULL : (IntervalAggState *) PG_GETARG_POINTER(0);
4206
4207 /* If there were no non-null inputs, return NULL */
4208 if (state == NULL || IA_TOTAL_COUNT(state) == 0)
4209 PG_RETURN_NULL();
4210
4211 /*
4212 * Aggregating infinities that all have the same sign produces infinity
4213 * with that sign. Aggregating infinities with different signs results in
4214 * an error.
4215 */
4216 if (state->pInfcount > 0 && state->nInfcount > 0)
4217 ereport(ERROR,
4218 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4219 errmsg("interval out of range")));
4220
4221 result = (Interval *) palloc(sizeof(Interval));
4222
4223 if (state->pInfcount > 0)
4224 INTERVAL_NOEND(result);
4225 else if (state->nInfcount > 0)
4226 INTERVAL_NOBEGIN(result);
4227 else
4228 memcpy(result, &state->sumX, sizeof(Interval));
4229
4230 PG_RETURN_INTERVAL_P(result);
4231 }
4232
4233 /* timestamp_age()
4234 * Calculate time difference while retaining year/month fields.
4235 * Note that this does not result in an accurate absolute time span
4236 * since year and month are out of context once the arithmetic
4237 * is done.
4238 */
4239 Datum
4240 timestamp_age(PG_FUNCTION_ARGS)
4241 {
4242 Timestamp dt1 = PG_GETARG_TIMESTAMP(0);
4243 Timestamp dt2 = PG_GETARG_TIMESTAMP(1);
4244 Interval *result;
4245 fsec_t fsec1,
4246 fsec2;
4247 struct pg_itm tt,
4248 *tm = &tt;
4249 struct pg_tm tt1,
4250 *tm1 = &tt1;
4251 struct pg_tm tt2,
4252 *tm2 = &tt2;
4253
4254 result = (Interval *) palloc(sizeof(Interval));
4255
4256 /*
4257 * Handle infinities.
4258 *
4259 * We treat anything that amounts to "infinity - infinity" as an error,
4260 * since the interval type has nothing equivalent to NaN.
4261 */
4262 if (TIMESTAMP_IS_NOBEGIN(dt1))
4263 {
4264 if (TIMESTAMP_IS_NOBEGIN(dt2))
4265 ereport(ERROR,
4266 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4267 errmsg("interval out of range")));
4268 else
4269 INTERVAL_NOBEGIN(result);
4270 }
4271 else if (TIMESTAMP_IS_NOEND(dt1))
4272 {
4273 if (TIMESTAMP_IS_NOEND(dt2))
4274 ereport(ERROR,
4275 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4276 errmsg("interval out of range")));
4277 else
4278 INTERVAL_NOEND(result);
4279 }
4280 else if (TIMESTAMP_IS_NOBEGIN(dt2))
4281 INTERVAL_NOEND(result);
4282 else if (TIMESTAMP_IS_NOEND(dt2))
4283 INTERVAL_NOBEGIN(result);
4284 else if (timestamp2tm(dt1, NULL, tm1, &fsec1, NULL, NULL) == 0 &&
4285 timestamp2tm(dt2, NULL, tm2, &fsec2, NULL, NULL) == 0)
4286 {
4287 /* form the symbolic difference */
4288 tm->tm_usec = fsec1 - fsec2;
4289 tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
4290 tm->tm_min = tm1->tm_min - tm2->tm_min;
4291 tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
4292 tm->tm_mday = tm1->tm_mday - tm2->tm_mday;
4293 tm->tm_mon = tm1->tm_mon - tm2->tm_mon;
4294 tm->tm_year = tm1->tm_year - tm2->tm_year;
4295
4296 /* flip sign if necessary... */
4297 if (dt1 < dt2)
4298 {
4299 tm->tm_usec = -tm->tm_usec;
4300 tm->tm_sec = -tm->tm_sec;
4301 tm->tm_min = -tm->tm_min;
4302 tm->tm_hour = -tm->tm_hour;
4303 tm->tm_mday = -tm->tm_mday;
4304 tm->tm_mon = -tm->tm_mon;
4305 tm->tm_year = -tm->tm_year;
4306 }
4307
4308 /* propagate any negative fields into the next higher field */
4309 while (tm->tm_usec < 0)
4310 {
4311 tm->tm_usec += USECS_PER_SEC;
4312 tm->tm_sec--;
4313 }
4314
4315 while (tm->tm_sec < 0)
4316 {
4317 tm->tm_sec += SECS_PER_MINUTE;
4318 tm->tm_min--;
4319 }
4320
4321 while (tm->tm_min < 0)
4322 {
4323 tm->tm_min += MINS_PER_HOUR;
4324 tm->tm_hour--;
4325 }
4326
4327 while (tm->tm_hour < 0)
4328 {
4329 tm->tm_hour += HOURS_PER_DAY;
4330 tm->tm_mday--;
4331 }
4332
4333 while (tm->tm_mday < 0)
4334 {
4335 if (dt1 < dt2)
4336 {
4337 tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
4338 tm->tm_mon--;
4339 }
4340 else
4341 {
4342 tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
4343 tm->tm_mon--;
4344 }
4345 }
4346
4347 while (tm->tm_mon < 0)
4348 {
4349 tm->tm_mon += MONTHS_PER_YEAR;
4350 tm->tm_year--;
4351 }
4352
4353 /* recover sign if necessary... */
4354 if (dt1 < dt2)
4355 {
4356 tm->tm_usec = -tm->tm_usec;
4357 tm->tm_sec = -tm->tm_sec;
4358 tm->tm_min = -tm->tm_min;
4359 tm->tm_hour = -tm->tm_hour;
4360 tm->tm_mday = -tm->tm_mday;
4361 tm->tm_mon = -tm->tm_mon;
4362 tm->tm_year = -tm->tm_year;
4363 }
4364
4365 if (itm2interval(tm, result) != 0)
4366 ereport(ERROR,
4367 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4368 errmsg("interval out of range")));
4369 }
4370 else
4371 ereport(ERROR,
4372 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4373 errmsg("timestamp out of range")));
4374
4375 PG_RETURN_INTERVAL_P(result);
4376 }
4377
4378
4379 /* timestamptz_age()
4380 * Calculate time difference while retaining year/month fields.
4381 * Note that this does not result in an accurate absolute time span
4382 * since year and month are out of context once the arithmetic
4383 * is done.
4384 */
4385 Datum
4386 timestamptz_age(PG_FUNCTION_ARGS)
4387 {
4388 TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0);
4389 TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1);
4390 Interval *result;
4391 fsec_t fsec1,
4392 fsec2;
4393 struct pg_itm tt,
4394 *tm = &tt;
4395 struct pg_tm tt1,
4396 *tm1 = &tt1;
4397 struct pg_tm tt2,
4398 *tm2 = &tt2;
4399 int tz1;
4400 int tz2;
4401
4402 result = (Interval *) palloc(sizeof(Interval));
4403
4404 /*
4405 * Handle infinities.
4406 *
4407 * We treat anything that amounts to "infinity - infinity" as an error,
4408 * since the interval type has nothing equivalent to NaN.
4409 */
4410 if (TIMESTAMP_IS_NOBEGIN(dt1))
4411 {
4412 if (TIMESTAMP_IS_NOBEGIN(dt2))
4413 ereport(ERROR,
4414 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4415 errmsg("interval out of range")));
4416 else
4417 INTERVAL_NOBEGIN(result);
4418 }
4419 else if (TIMESTAMP_IS_NOEND(dt1))
4420 {
4421 if (TIMESTAMP_IS_NOEND(dt2))
4422 ereport(ERROR,
4423 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4424 errmsg("interval out of range")));
4425 else
4426 INTERVAL_NOEND(result);
4427 }
4428 else if (TIMESTAMP_IS_NOBEGIN(dt2))
4429 INTERVAL_NOEND(result);
4430 else if (TIMESTAMP_IS_NOEND(dt2))
4431 INTERVAL_NOBEGIN(result);
4432 else if (timestamp2tm(dt1, &tz1, tm1, &fsec1, NULL, NULL) == 0 &&
4433 timestamp2tm(dt2, &tz2, tm2, &fsec2, NULL, NULL) == 0)
4434 {
4435 /* form the symbolic difference */
4436 tm->tm_usec = fsec1 - fsec2;
4437 tm->tm_sec = tm1->tm_sec - tm2->tm_sec;
4438 tm->tm_min = tm1->tm_min - tm2->tm_min;
4439 tm->tm_hour = tm1->tm_hour - tm2->tm_hour;
4440 tm->tm_mday = tm1->tm_mday - tm2->tm_mday;
4441 tm->tm_mon = tm1->tm_mon - tm2->tm_mon;
4442 tm->tm_year = tm1->tm_year - tm2->tm_year;
4443
4444 /* flip sign if necessary... */
4445 if (dt1 < dt2)
4446 {
4447 tm->tm_usec = -tm->tm_usec;
4448 tm->tm_sec = -tm->tm_sec;
4449 tm->tm_min = -tm->tm_min;
4450 tm->tm_hour = -tm->tm_hour;
4451 tm->tm_mday = -tm->tm_mday;
4452 tm->tm_mon = -tm->tm_mon;
4453 tm->tm_year = -tm->tm_year;
4454 }
4455
4456 /* propagate any negative fields into the next higher field */
4457 while (tm->tm_usec < 0)
4458 {
4459 tm->tm_usec += USECS_PER_SEC;
4460 tm->tm_sec--;
4461 }
4462
4463 while (tm->tm_sec < 0)
4464 {
4465 tm->tm_sec += SECS_PER_MINUTE;
4466 tm->tm_min--;
4467 }
4468
4469 while (tm->tm_min < 0)
4470 {
4471 tm->tm_min += MINS_PER_HOUR;
4472 tm->tm_hour--;
4473 }
4474
4475 while (tm->tm_hour < 0)
4476 {
4477 tm->tm_hour += HOURS_PER_DAY;
4478 tm->tm_mday--;
4479 }
4480
4481 while (tm->tm_mday < 0)
4482 {
4483 if (dt1 < dt2)
4484 {
4485 tm->tm_mday += day_tab[isleap(tm1->tm_year)][tm1->tm_mon - 1];
4486 tm->tm_mon--;
4487 }
4488 else
4489 {
4490 tm->tm_mday += day_tab[isleap(tm2->tm_year)][tm2->tm_mon - 1];
4491 tm->tm_mon--;
4492 }
4493 }
4494
4495 while (tm->tm_mon < 0)
4496 {
4497 tm->tm_mon += MONTHS_PER_YEAR;
4498 tm->tm_year--;
4499 }
4500
4501 /*
4502 * Note: we deliberately ignore any difference between tz1 and tz2.
4503 */
4504
4505 /* recover sign if necessary... */
4506 if (dt1 < dt2)
4507 {
4508 tm->tm_usec = -tm->tm_usec;
4509 tm->tm_sec = -tm->tm_sec;
4510 tm->tm_min = -tm->tm_min;
4511 tm->tm_hour = -tm->tm_hour;
4512 tm->tm_mday = -tm->tm_mday;
4513 tm->tm_mon = -tm->tm_mon;
4514 tm->tm_year = -tm->tm_year;
4515 }
4516
4517 if (itm2interval(tm, result) != 0)
4518 ereport(ERROR,
4519 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4520 errmsg("interval out of range")));
4521 }
4522 else
4523 ereport(ERROR,
4524 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4525 errmsg("timestamp out of range")));
4526
4527 PG_RETURN_INTERVAL_P(result);
4528 }
4529
4530
4531 /*----------------------------------------------------------
4532 * Conversion operators.
4533 *---------------------------------------------------------*/
4534
4535
4536 /* timestamp_bin()
4537 * Bin timestamp into specified interval.
4538 */
4539 Datum
4540 timestamp_bin(PG_FUNCTION_ARGS)
4541 {
4542 Interval *stride = PG_GETARG_INTERVAL_P(0);
4543 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
4544 Timestamp origin = PG_GETARG_TIMESTAMP(2);
4545 Timestamp result,
4546 stride_usecs,
4547 tm_diff,
4548 tm_modulo,
4549 tm_delta;
4550
4551 if (TIMESTAMP_NOT_FINITE(timestamp))
4552 PG_RETURN_TIMESTAMP(timestamp);
4553
4554 if (TIMESTAMP_NOT_FINITE(origin))
4555 ereport(ERROR,
4556 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4557 errmsg("origin out of range")));
4558
4559 if (INTERVAL_NOT_FINITE(stride))
4560 ereport(ERROR,
4561 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4562 errmsg("timestamps cannot be binned into infinite intervals")));
4563
4564 if (stride->month != 0)
4565 ereport(ERROR,
4566 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4567 errmsg("timestamps cannot be binned into intervals containing months or years")));
4568
4569 if (unlikely(pg_mul_s64_overflow(stride->day, USECS_PER_DAY, &stride_usecs)) ||
4570 unlikely(pg_add_s64_overflow(stride_usecs, stride->time, &stride_usecs)))
4571 ereport(ERROR,
4572 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4573 errmsg("interval out of range")));
4574
4575 if (stride_usecs <= 0)
4576 ereport(ERROR,
4577 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4578 errmsg("stride must be greater than zero")));
4579
4580 if (unlikely(pg_sub_s64_overflow(timestamp, origin, &tm_diff)))
4581 ereport(ERROR,
4582 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4583 errmsg("interval out of range")));
4584
4585 /* These calculations cannot overflow */
4586 tm_modulo = tm_diff % stride_usecs;
4587 tm_delta = tm_diff - tm_modulo;
4588 result = origin + tm_delta;
4589
4590 /*
4591 * We want to round towards -infinity, not 0, when tm_diff is negative and
4592 * not a multiple of stride_usecs. This adjustment *can* cause overflow,
4593 * since the result might now be out of the range origin .. timestamp.
4594 */
4595 if (tm_modulo < 0)
4596 {
4597 if (unlikely(pg_sub_s64_overflow(result, stride_usecs, &result)) ||
4598 !IS_VALID_TIMESTAMP(result))
4599 ereport(ERROR,
4600 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4601 errmsg("timestamp out of range")));
4602 }
4603
4604 PG_RETURN_TIMESTAMP(result);
4605 }
4606
4607 /* timestamp_trunc()
4608 * Truncate timestamp to specified units.
4609 */
4610 Datum
4611 timestamp_trunc(PG_FUNCTION_ARGS)
4612 {
4613 text *units = PG_GETARG_TEXT_PP(0);
4614 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
4615 Timestamp result;
4616 int type,
4617 val;
4618 char *lowunits;
4619 fsec_t fsec;
4620 struct pg_tm tt,
4621 *tm = &tt;
4622
4623 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4624 VARSIZE_ANY_EXHDR(units),
4625 false);
4626
4627 type = DecodeUnits(0, lowunits, &val);
4628
4629 if (type == UNITS)
4630 {
4631 if (TIMESTAMP_NOT_FINITE(timestamp))
4632 {
4633 /*
4634 * Errors thrown here for invalid units should exactly match those
4635 * below, else there will be unexpected discrepancies between
4636 * finite- and infinite-input cases.
4637 */
4638 switch (val)
4639 {
4640 case DTK_WEEK:
4641 case DTK_MILLENNIUM:
4642 case DTK_CENTURY:
4643 case DTK_DECADE:
4644 case DTK_YEAR:
4645 case DTK_QUARTER:
4646 case DTK_MONTH:
4647 case DTK_DAY:
4648 case DTK_HOUR:
4649 case DTK_MINUTE:
4650 case DTK_SECOND:
4651 case DTK_MILLISEC:
4652 case DTK_MICROSEC:
4653 PG_RETURN_TIMESTAMP(timestamp);
4654 break;
4655 default:
4656 ereport(ERROR,
4657 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4658 errmsg("unit \"%s\" not supported for type %s",
4659 lowunits, format_type_be(TIMESTAMPOID))));
4660 result = 0;
4661 }
4662 }
4663
4664 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
4665 ereport(ERROR,
4666 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4667 errmsg("timestamp out of range")));
4668
4669 switch (val)
4670 {
4671 case DTK_WEEK:
4672 {
4673 int woy;
4674
4675 woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
4676
4677 /*
4678 * If it is week 52/53 and the month is January, then the
4679 * week must belong to the previous year. Also, some
4680 * December dates belong to the next year.
4681 */
4682 if (woy >= 52 && tm->tm_mon == 1)
4683 --tm->tm_year;
4684 if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
4685 ++tm->tm_year;
4686 isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
4687 tm->tm_hour = 0;
4688 tm->tm_min = 0;
4689 tm->tm_sec = 0;
4690 fsec = 0;
4691 break;
4692 }
4693 case DTK_MILLENNIUM:
4694 /* see comments in timestamptz_trunc */
4695 if (tm->tm_year > 0)
4696 tm->tm_year = ((tm->tm_year + 999) / 1000) * 1000 - 999;
4697 else
4698 tm->tm_year = -((999 - (tm->tm_year - 1)) / 1000) * 1000 + 1;
4699 /* FALL THRU */
4700 case DTK_CENTURY:
4701 /* see comments in timestamptz_trunc */
4702 if (tm->tm_year > 0)
4703 tm->tm_year = ((tm->tm_year + 99) / 100) * 100 - 99;
4704 else
4705 tm->tm_year = -((99 - (tm->tm_year - 1)) / 100) * 100 + 1;
4706 /* FALL THRU */
4707 case DTK_DECADE:
4708 /* see comments in timestamptz_trunc */
4709 if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
4710 {
4711 if (tm->tm_year > 0)
4712 tm->tm_year = (tm->tm_year / 10) * 10;
4713 else
4714 tm->tm_year = -((8 - (tm->tm_year - 1)) / 10) * 10;
4715 }
4716 /* FALL THRU */
4717 case DTK_YEAR:
4718 tm->tm_mon = 1;
4719 /* FALL THRU */
4720 case DTK_QUARTER:
4721 tm->tm_mon = (3 * ((tm->tm_mon - 1) / 3)) + 1;
4722 /* FALL THRU */
4723 case DTK_MONTH:
4724 tm->tm_mday = 1;
4725 /* FALL THRU */
4726 case DTK_DAY:
4727 tm->tm_hour = 0;
4728 /* FALL THRU */
4729 case DTK_HOUR:
4730 tm->tm_min = 0;
4731 /* FALL THRU */
4732 case DTK_MINUTE:
4733 tm->tm_sec = 0;
4734 /* FALL THRU */
4735 case DTK_SECOND:
4736 fsec = 0;
4737 break;
4738
4739 case DTK_MILLISEC:
4740 fsec = (fsec / 1000) * 1000;
4741 break;
4742
4743 case DTK_MICROSEC:
4744 break;
4745
4746 default:
4747 ereport(ERROR,
4748 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4749 errmsg("unit \"%s\" not supported for type %s",
4750 lowunits, format_type_be(TIMESTAMPOID))));
4751 result = 0;
4752 }
4753
4754 if (tm2timestamp(tm, fsec, NULL, &result) != 0)
4755 ereport(ERROR,
4756 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4757 errmsg("timestamp out of range")));
4758 }
4759 else
4760 {
4761 ereport(ERROR,
4762 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
4763 errmsg("unit \"%s\" not recognized for type %s",
4764 lowunits, format_type_be(TIMESTAMPOID))));
4765 result = 0;
4766 }
4767
4768 PG_RETURN_TIMESTAMP(result);
4769 }
4770
4771 /* timestamptz_bin()
4772 * Bin timestamptz into specified interval using specified origin.
4773 */
4774 Datum
4775 timestamptz_bin(PG_FUNCTION_ARGS)
4776 {
4777 Interval *stride = PG_GETARG_INTERVAL_P(0);
4778 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
4779 TimestampTz origin = PG_GETARG_TIMESTAMPTZ(2);
4780 TimestampTz result,
4781 stride_usecs,
4782 tm_diff,
4783 tm_modulo,
4784 tm_delta;
4785
4786 if (TIMESTAMP_NOT_FINITE(timestamp))
4787 PG_RETURN_TIMESTAMPTZ(timestamp);
4788
4789 if (TIMESTAMP_NOT_FINITE(origin))
4790 ereport(ERROR,
4791 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4792 errmsg("origin out of range")));
4793
4794 if (INTERVAL_NOT_FINITE(stride))
4795 ereport(ERROR,
4796 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4797 errmsg("timestamps cannot be binned into infinite intervals")));
4798
4799 if (stride->month != 0)
4800 ereport(ERROR,
4801 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4802 errmsg("timestamps cannot be binned into intervals containing months or years")));
4803
4804 if (unlikely(pg_mul_s64_overflow(stride->day, USECS_PER_DAY, &stride_usecs)) ||
4805 unlikely(pg_add_s64_overflow(stride_usecs, stride->time, &stride_usecs)))
4806 ereport(ERROR,
4807 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4808 errmsg("interval out of range")));
4809
4810 if (stride_usecs <= 0)
4811 ereport(ERROR,
4812 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4813 errmsg("stride must be greater than zero")));
4814
4815 if (unlikely(pg_sub_s64_overflow(timestamp, origin, &tm_diff)))
4816 ereport(ERROR,
4817 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4818 errmsg("interval out of range")));
4819
4820 /* These calculations cannot overflow */
4821 tm_modulo = tm_diff % stride_usecs;
4822 tm_delta = tm_diff - tm_modulo;
4823 result = origin + tm_delta;
4824
4825 /*
4826 * We want to round towards -infinity, not 0, when tm_diff is negative and
4827 * not a multiple of stride_usecs. This adjustment *can* cause overflow,
4828 * since the result might now be out of the range origin .. timestamp.
4829 */
4830 if (tm_modulo < 0)
4831 {
4832 if (unlikely(pg_sub_s64_overflow(result, stride_usecs, &result)) ||
4833 !IS_VALID_TIMESTAMP(result))
4834 ereport(ERROR,
4835 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4836 errmsg("timestamp out of range")));
4837 }
4838
4839 PG_RETURN_TIMESTAMPTZ(result);
4840 }
4841
4842 /*
4843 * Common code for timestamptz_trunc() and timestamptz_trunc_zone().
4844 *
4845 * tzp identifies the zone to truncate with respect to. We assume
4846 * infinite timestamps have already been rejected.
4847 */
4848 static TimestampTz
4849 timestamptz_trunc_internal(text *units, TimestampTz timestamp, pg_tz *tzp)
4850 {
4851 TimestampTz result;
4852 int tz;
4853 int type,
4854 val;
4855 bool redotz = false;
4856 char *lowunits;
4857 fsec_t fsec;
4858 struct pg_tm tt,
4859 *tm = &tt;
4860
4861 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
4862 VARSIZE_ANY_EXHDR(units),
4863 false);
4864
4865 type = DecodeUnits(0, lowunits, &val);
4866
4867 if (type == UNITS)
4868 {
4869 if (TIMESTAMP_NOT_FINITE(timestamp))
4870 {
4871 /*
4872 * Errors thrown here for invalid units should exactly match those
4873 * below, else there will be unexpected discrepancies between
4874 * finite- and infinite-input cases.
4875 */
4876 switch (val)
4877 {
4878 case DTK_WEEK:
4879 case DTK_MILLENNIUM:
4880 case DTK_CENTURY:
4881 case DTK_DECADE:
4882 case DTK_YEAR:
4883 case DTK_QUARTER:
4884 case DTK_MONTH:
4885 case DTK_DAY:
4886 case DTK_HOUR:
4887 case DTK_MINUTE:
4888 case DTK_SECOND:
4889 case DTK_MILLISEC:
4890 case DTK_MICROSEC:
4891 PG_RETURN_TIMESTAMPTZ(timestamp);
4892 break;
4893
4894 default:
4895 ereport(ERROR,
4896 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4897 errmsg("unit \"%s\" not supported for type %s",
4898 lowunits, format_type_be(TIMESTAMPTZOID))));
4899 result = 0;
4900 }
4901 }
4902
4903 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, tzp) != 0)
4904 ereport(ERROR,
4905 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
4906 errmsg("timestamp out of range")));
4907
4908 switch (val)
4909 {
4910 case DTK_WEEK:
4911 {
4912 int woy;
4913
4914 woy = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
4915
4916 /*
4917 * If it is week 52/53 and the month is January, then the
4918 * week must belong to the previous year. Also, some
4919 * December dates belong to the next year.
4920 */
4921 if (woy >= 52 && tm->tm_mon == 1)
4922 --tm->tm_year;
4923 if (woy <= 1 && tm->tm_mon == MONTHS_PER_YEAR)
4924 ++tm->tm_year;
4925 isoweek2date(woy, &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
4926 tm->tm_hour = 0;
4927 tm->tm_min = 0;
4928 tm->tm_sec = 0;
4929 fsec = 0;
4930 redotz = true;
4931 break;
4932 }
4933 /* one may consider DTK_THOUSAND and DTK_HUNDRED... */
4934 case DTK_MILLENNIUM:
4935
4936 /*
4937 * truncating to the millennium? what is this supposed to
4938 * mean? let us put the first year of the millennium... i.e.
4939 * -1000, 1, 1001, 2001...
4940 */
4941 if (tm->tm_year > 0)
4942 tm->tm_year = ((tm->tm_year + 999) / 1000) * 1000 - 999;
4943 else
4944 tm->tm_year = -((999 - (tm->tm_year - 1)) / 1000) * 1000 + 1;
4945 /* FALL THRU */
4946 case DTK_CENTURY:
4947 /* truncating to the century? as above: -100, 1, 101... */
4948 if (tm->tm_year > 0)
4949 tm->tm_year = ((tm->tm_year + 99) / 100) * 100 - 99;
4950 else
4951 tm->tm_year = -((99 - (tm->tm_year - 1)) / 100) * 100 + 1;
4952 /* FALL THRU */
4953 case DTK_DECADE:
4954
4955 /*
4956 * truncating to the decade? first year of the decade. must
4957 * not be applied if year was truncated before!
4958 */
4959 if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
4960 {
4961 if (tm->tm_year > 0)
4962 tm->tm_year = (tm->tm_year / 10) * 10;
4963 else
4964 tm->tm_year = -((8 - (tm->tm_year - 1)) / 10) * 10;
4965 }
4966 /* FALL THRU */
4967 case DTK_YEAR:
4968 tm->tm_mon = 1;
4969 /* FALL THRU */
4970 case DTK_QUARTER:
4971 tm->tm_mon = (3 * ((tm->tm_mon - 1) / 3)) + 1;
4972 /* FALL THRU */
4973 case DTK_MONTH:
4974 tm->tm_mday = 1;
4975 /* FALL THRU */
4976 case DTK_DAY:
4977 tm->tm_hour = 0;
4978 redotz = true; /* for all cases >= DAY */
4979 /* FALL THRU */
4980 case DTK_HOUR:
4981 tm->tm_min = 0;
4982 /* FALL THRU */
4983 case DTK_MINUTE:
4984 tm->tm_sec = 0;
4985 /* FALL THRU */
4986 case DTK_SECOND:
4987 fsec = 0;
4988 break;
4989 case DTK_MILLISEC:
4990 fsec = (fsec / 1000) * 1000;
4991 break;
4992 case DTK_MICROSEC:
4993 break;
4994
4995 default:
4996 ereport(ERROR,
4997 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
4998 errmsg("unit \"%s\" not supported for type %s",
4999 lowunits, format_type_be(TIMESTAMPTZOID))));
5000 result = 0;
5001 }
5002
5003 if (redotz)
5004 tz = DetermineTimeZoneOffset(tm, tzp);
5005
5006 if (tm2timestamp(tm, fsec, &tz, &result) != 0)
5007 ereport(ERROR,
5008 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5009 errmsg("timestamp out of range")));
5010 }
5011 else
5012 {
5013 ereport(ERROR,
5014 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5015 errmsg("unit \"%s\" not recognized for type %s",
5016 lowunits, format_type_be(TIMESTAMPTZOID))));
5017 result = 0;
5018 }
5019
5020 return result;
5021 }
5022
5023 /* timestamptz_trunc()
5024 * Truncate timestamptz to specified units in session timezone.
5025 */
5026 Datum
5027 timestamptz_trunc(PG_FUNCTION_ARGS)
5028 {
5029 text *units = PG_GETARG_TEXT_PP(0);
5030 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
5031 TimestampTz result;
5032
5033 result = timestamptz_trunc_internal(units, timestamp, session_timezone);
5034
5035 PG_RETURN_TIMESTAMPTZ(result);
5036 }
5037
5038 /* timestamptz_trunc_zone()
5039 * Truncate timestamptz to specified units in specified timezone.
5040 */
5041 Datum
5042 timestamptz_trunc_zone(PG_FUNCTION_ARGS)
5043 {
5044 text *units = PG_GETARG_TEXT_PP(0);
5045 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
5046 text *zone = PG_GETARG_TEXT_PP(2);
5047 TimestampTz result;
5048 pg_tz *tzp;
5049
5050 /*
5051 * Look up the requested timezone.
5052 */
5053 tzp = lookup_timezone(zone);
5054
5055 result = timestamptz_trunc_internal(units, timestamp, tzp);
5056
5057 PG_RETURN_TIMESTAMPTZ(result);
5058 }
5059
5060 /* interval_trunc()
5061 * Extract specified field from interval.
5062 */
5063 Datum
5064 interval_trunc(PG_FUNCTION_ARGS)
5065 {
5066 text *units = PG_GETARG_TEXT_PP(0);
5067 Interval *interval = PG_GETARG_INTERVAL_P(1);
5068 Interval *result;
5069 int type,
5070 val;
5071 char *lowunits;
5072 struct pg_itm tt,
5073 *tm = &tt;
5074
5075 result = (Interval *) palloc(sizeof(Interval));
5076
5077 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
5078 VARSIZE_ANY_EXHDR(units),
5079 false);
5080
5081 type = DecodeUnits(0, lowunits, &val);
5082
5083 if (type == UNITS)
5084 {
5085 if (INTERVAL_NOT_FINITE(interval))
5086 {
5087 /*
5088 * Errors thrown here for invalid units should exactly match those
5089 * below, else there will be unexpected discrepancies between
5090 * finite- and infinite-input cases.
5091 */
5092 switch (val)
5093 {
5094 case DTK_MILLENNIUM:
5095 case DTK_CENTURY:
5096 case DTK_DECADE:
5097 case DTK_YEAR:
5098 case DTK_QUARTER:
5099 case DTK_MONTH:
5100 case DTK_DAY:
5101 case DTK_HOUR:
5102 case DTK_MINUTE:
5103 case DTK_SECOND:
5104 case DTK_MILLISEC:
5105 case DTK_MICROSEC:
5106 memcpy(result, interval, sizeof(Interval));
5107 PG_RETURN_INTERVAL_P(result);
5108 break;
5109
5110 default:
5111 ereport(ERROR,
5112 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5113 errmsg("unit \"%s\" not supported for type %s",
5114 lowunits, format_type_be(INTERVALOID)),
5115 (val == DTK_WEEK) ? errdetail("Months usually have fractional weeks.") : 0));
5116 result = 0;
5117 }
5118 }
5119
5120 interval2itm(*interval, tm);
5121 switch (val)
5122 {
5123 case DTK_MILLENNIUM:
5124 /* caution: C division may have negative remainder */
5125 tm->tm_year = (tm->tm_year / 1000) * 1000;
5126 /* FALL THRU */
5127 case DTK_CENTURY:
5128 /* caution: C division may have negative remainder */
5129 tm->tm_year = (tm->tm_year / 100) * 100;
5130 /* FALL THRU */
5131 case DTK_DECADE:
5132 /* caution: C division may have negative remainder */
5133 tm->tm_year = (tm->tm_year / 10) * 10;
5134 /* FALL THRU */
5135 case DTK_YEAR:
5136 tm->tm_mon = 0;
5137 /* FALL THRU */
5138 case DTK_QUARTER:
5139 tm->tm_mon = 3 * (tm->tm_mon / 3);
5140 /* FALL THRU */
5141 case DTK_MONTH:
5142 tm->tm_mday = 0;
5143 /* FALL THRU */
5144 case DTK_DAY:
5145 tm->tm_hour = 0;
5146 /* FALL THRU */
5147 case DTK_HOUR:
5148 tm->tm_min = 0;
5149 /* FALL THRU */
5150 case DTK_MINUTE:
5151 tm->tm_sec = 0;
5152 /* FALL THRU */
5153 case DTK_SECOND:
5154 tm->tm_usec = 0;
5155 break;
5156 case DTK_MILLISEC:
5157 tm->tm_usec = (tm->tm_usec / 1000) * 1000;
5158 break;
5159 case DTK_MICROSEC:
5160 break;
5161
5162 default:
5163 ereport(ERROR,
5164 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5165 errmsg("unit \"%s\" not supported for type %s",
5166 lowunits, format_type_be(INTERVALOID)),
5167 (val == DTK_WEEK) ? errdetail("Months usually have fractional weeks.") : 0));
5168 }
5169
5170 if (itm2interval(tm, result) != 0)
5171 ereport(ERROR,
5172 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5173 errmsg("interval out of range")));
5174 }
5175 else
5176 {
5177 ereport(ERROR,
5178 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5179 errmsg("unit \"%s\" not recognized for type %s",
5180 lowunits, format_type_be(INTERVALOID))));
5181 }
5182
5183 PG_RETURN_INTERVAL_P(result);
5184 }
5185
5186 /* isoweek2j()
5187 *
5188 * Return the Julian day which corresponds to the first day (Monday) of the given ISO 8601 year and week.
5189 * Julian days are used to convert between ISO week dates and Gregorian dates.
5190 *
5191 * XXX: This function has integer overflow hazards, but restructuring it to
5192 * work with the soft-error handling that its callers do is likely more
5193 * trouble than it's worth.
5194 */
5195 int
5196 isoweek2j(int year, int week)
5197 {
5198 int day0,
5199 day4;
5200
5201 /* fourth day of current year */
5202 day4 = date2j(year, 1, 4);
5203
5204 /* day0 == offset to first day of week (Monday) */
5205 day0 = j2day(day4 - 1);
5206
5207 return ((week - 1) * 7) + (day4 - day0);
5208 }
5209
5210 /* isoweek2date()
5211 * Convert ISO week of year number to date.
5212 * The year field must be specified with the ISO year!
5213 * karel 2000/08/07
5214 */
5215 void
5216 isoweek2date(int woy, int *year, int *mon, int *mday)
5217 {
5218 j2date(isoweek2j(*year, woy), year, mon, mday);
5219 }
5220
5221 /* isoweekdate2date()
5222 *
5223 * Convert an ISO 8601 week date (ISO year, ISO week) into a Gregorian date.
5224 * Gregorian day of week sent so weekday strings can be supplied.
5225 * Populates year, mon, and mday with the correct Gregorian values.
5226 * year must be passed in as the ISO year.
5227 */
5228 void
5229 isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday)
5230 {
5231 int jday;
5232
5233 jday = isoweek2j(*year, isoweek);
5234 /* convert Gregorian week start (Sunday=1) to ISO week start (Monday=1) */
5235 if (wday > 1)
5236 jday += wday - 2;
5237 else
5238 jday += 6;
5239 j2date(jday, year, mon, mday);
5240 }
5241
5242 /* date2isoweek()
5243 *
5244 * Returns ISO week number of year.
5245 */
5246 int
5247 date2isoweek(int year, int mon, int mday)
5248 {
5249 float8 result;
5250 int day0,
5251 day4,
5252 dayn;
5253
5254 /* current day */
5255 dayn = date2j(year, mon, mday);
5256
5257 /* fourth day of current year */
5258 day4 = date2j(year, 1, 4);
5259
5260 /* day0 == offset to first day of week (Monday) */
5261 day0 = j2day(day4 - 1);
5262
5263 /*
5264 * We need the first week containing a Thursday, otherwise this day falls
5265 * into the previous year for purposes of counting weeks
5266 */
5267 if (dayn < day4 - day0)
5268 {
5269 day4 = date2j(year - 1, 1, 4);
5270
5271 /* day0 == offset to first day of week (Monday) */
5272 day0 = j2day(day4 - 1);
5273 }
5274
5275 result = (dayn - (day4 - day0)) / 7 + 1;
5276
5277 /*
5278 * Sometimes the last few days in a year will fall into the first week of
5279 * the next year, so check for this.
5280 */
5281 if (result >= 52)
5282 {
5283 day4 = date2j(year + 1, 1, 4);
5284
5285 /* day0 == offset to first day of week (Monday) */
5286 day0 = j2day(day4 - 1);
5287
5288 if (dayn >= day4 - day0)
5289 result = (dayn - (day4 - day0)) / 7 + 1;
5290 }
5291
5292 return (int) result;
5293 }
5294
5295
5296 /* date2isoyear()
5297 *
5298 * Returns ISO 8601 year number.
5299 * Note: zero or negative results follow the year-zero-exists convention.
5300 */
5301 int
5302 date2isoyear(int year, int mon, int mday)
5303 {
5304 float8 result;
5305 int day0,
5306 day4,
5307 dayn;
5308
5309 /* current day */
5310 dayn = date2j(year, mon, mday);
5311
5312 /* fourth day of current year */
5313 day4 = date2j(year, 1, 4);
5314
5315 /* day0 == offset to first day of week (Monday) */
5316 day0 = j2day(day4 - 1);
5317
5318 /*
5319 * We need the first week containing a Thursday, otherwise this day falls
5320 * into the previous year for purposes of counting weeks
5321 */
5322 if (dayn < day4 - day0)
5323 {
5324 day4 = date2j(year - 1, 1, 4);
5325
5326 /* day0 == offset to first day of week (Monday) */
5327 day0 = j2day(day4 - 1);
5328
5329 year--;
5330 }
5331
5332 result = (dayn - (day4 - day0)) / 7 + 1;
5333
5334 /*
5335 * Sometimes the last few days in a year will fall into the first week of
5336 * the next year, so check for this.
5337 */
5338 if (result >= 52)
5339 {
5340 day4 = date2j(year + 1, 1, 4);
5341
5342 /* day0 == offset to first day of week (Monday) */
5343 day0 = j2day(day4 - 1);
5344
5345 if (dayn >= day4 - day0)
5346 year++;
5347 }
5348
5349 return year;
5350 }
5351
5352
5353 /* date2isoyearday()
5354 *
5355 * Returns the ISO 8601 day-of-year, given a Gregorian year, month and day.
5356 * Possible return values are 1 through 371 (364 in non-leap years).
5357 */
5358 int
5359 date2isoyearday(int year, int mon, int mday)
5360 {
5361 return date2j(year, mon, mday) - isoweek2j(date2isoyear(year, mon, mday), 1) + 1;
5362 }
5363
5364 /*
5365 * NonFiniteTimestampTzPart
5366 *
5367 * Used by timestamp_part and timestamptz_part when extracting from infinite
5368 * timestamp[tz]. Returns +/-Infinity if that is the appropriate result,
5369 * otherwise returns zero (which should be taken as meaning to return NULL).
5370 *
5371 * Errors thrown here for invalid units should exactly match those that
5372 * would be thrown in the calling functions, else there will be unexpected
5373 * discrepancies between finite- and infinite-input cases.
5374 */
5375 static float8
5376 NonFiniteTimestampTzPart(int type, int unit, char *lowunits,
5377 bool isNegative, bool isTz)
5378 {
5379 if ((type != UNITS) && (type != RESERV))
5380 ereport(ERROR,
5381 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5382 errmsg("unit \"%s\" not recognized for type %s",
5383 lowunits,
5384 format_type_be(isTz ? TIMESTAMPTZOID : TIMESTAMPOID))));
5385
5386 switch (unit)
5387 {
5388 /* Oscillating units */
5389 case DTK_MICROSEC:
5390 case DTK_MILLISEC:
5391 case DTK_SECOND:
5392 case DTK_MINUTE:
5393 case DTK_HOUR:
5394 case DTK_DAY:
5395 case DTK_MONTH:
5396 case DTK_QUARTER:
5397 case DTK_WEEK:
5398 case DTK_DOW:
5399 case DTK_ISODOW:
5400 case DTK_DOY:
5401 case DTK_TZ:
5402 case DTK_TZ_MINUTE:
5403 case DTK_TZ_HOUR:
5404 return 0.0;
5405
5406 /* Monotonically-increasing units */
5407 case DTK_YEAR:
5408 case DTK_DECADE:
5409 case DTK_CENTURY:
5410 case DTK_MILLENNIUM:
5411 case DTK_JULIAN:
5412 case DTK_ISOYEAR:
5413 case DTK_EPOCH:
5414 if (isNegative)
5415 return -get_float8_infinity();
5416 else
5417 return get_float8_infinity();
5418
5419 default:
5420 ereport(ERROR,
5421 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5422 errmsg("unit \"%s\" not supported for type %s",
5423 lowunits,
5424 format_type_be(isTz ? TIMESTAMPTZOID : TIMESTAMPOID))));
5425 return 0.0; /* keep compiler quiet */
5426 }
5427 }
5428
5429 /* timestamp_part() and extract_timestamp()
5430 * Extract specified field from timestamp.
5431 */
5432 static Datum
5433 timestamp_part_common(PG_FUNCTION_ARGS, bool retnumeric)
5434 {
5435 text *units = PG_GETARG_TEXT_PP(0);
5436 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
5437 int64 intresult;
5438 Timestamp epoch;
5439 int type,
5440 val;
5441 char *lowunits;
5442 fsec_t fsec;
5443 struct pg_tm tt,
5444 *tm = &tt;
5445
5446 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
5447 VARSIZE_ANY_EXHDR(units),
5448 false);
5449
5450 type = DecodeUnits(0, lowunits, &val);
5451 if (type == UNKNOWN_FIELD)
5452 type = DecodeSpecial(0, lowunits, &val);
5453
5454 if (TIMESTAMP_NOT_FINITE(timestamp))
5455 {
5456 double r = NonFiniteTimestampTzPart(type, val, lowunits,
5457 TIMESTAMP_IS_NOBEGIN(timestamp),
5458 false);
5459
5460 if (r != 0.0)
5461 {
5462 if (retnumeric)
5463 {
5464 if (r < 0)
5465 return DirectFunctionCall3(numeric_in,
5466 CStringGetDatum("-Infinity"),
5467 ObjectIdGetDatum(InvalidOid),
5468 Int32GetDatum(-1));
5469 else if (r > 0)
5470 return DirectFunctionCall3(numeric_in,
5471 CStringGetDatum("Infinity"),
5472 ObjectIdGetDatum(InvalidOid),
5473 Int32GetDatum(-1));
5474 }
5475 else
5476 PG_RETURN_FLOAT8(r);
5477 }
5478 else
5479 PG_RETURN_NULL();
5480 }
5481
5482 if (type == UNITS)
5483 {
5484 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
5485 ereport(ERROR,
5486 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5487 errmsg("timestamp out of range")));
5488
5489 switch (val)
5490 {
5491 case DTK_MICROSEC:
5492 intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
5493 break;
5494
5495 case DTK_MILLISEC:
5496 if (retnumeric)
5497 /*---
5498 * tm->tm_sec * 1000 + fsec / 1000
5499 * = (tm->tm_sec * 1'000'000 + fsec) / 1000
5500 */
5501 PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
5502 else
5503 PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
5504 break;
5505
5506 case DTK_SECOND:
5507 if (retnumeric)
5508 /*---
5509 * tm->tm_sec + fsec / 1'000'000
5510 * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
5511 */
5512 PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
5513 else
5514 PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
5515 break;
5516
5517 case DTK_MINUTE:
5518 intresult = tm->tm_min;
5519 break;
5520
5521 case DTK_HOUR:
5522 intresult = tm->tm_hour;
5523 break;
5524
5525 case DTK_DAY:
5526 intresult = tm->tm_mday;
5527 break;
5528
5529 case DTK_MONTH:
5530 intresult = tm->tm_mon;
5531 break;
5532
5533 case DTK_QUARTER:
5534 intresult = (tm->tm_mon - 1) / 3 + 1;
5535 break;
5536
5537 case DTK_WEEK:
5538 intresult = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
5539 break;
5540
5541 case DTK_YEAR:
5542 if (tm->tm_year > 0)
5543 intresult = tm->tm_year;
5544 else
5545 /* there is no year 0, just 1 BC and 1 AD */
5546 intresult = tm->tm_year - 1;
5547 break;
5548
5549 case DTK_DECADE:
5550
5551 /*
5552 * what is a decade wrt dates? let us assume that decade 199
5553 * is 1990 thru 1999... decade 0 starts on year 1 BC, and -1
5554 * is 11 BC thru 2 BC...
5555 */
5556 if (tm->tm_year >= 0)
5557 intresult = tm->tm_year / 10;
5558 else
5559 intresult = -((8 - (tm->tm_year - 1)) / 10);
5560 break;
5561
5562 case DTK_CENTURY:
5563
5564 /* ----
5565 * centuries AD, c>0: year in [ (c-1)* 100 + 1 : c*100 ]
5566 * centuries BC, c<0: year in [ c*100 : (c+1) * 100 - 1]
5567 * there is no number 0 century.
5568 * ----
5569 */
5570 if (tm->tm_year > 0)
5571 intresult = (tm->tm_year + 99) / 100;
5572 else
5573 /* caution: C division may have negative remainder */
5574 intresult = -((99 - (tm->tm_year - 1)) / 100);
5575 break;
5576
5577 case DTK_MILLENNIUM:
5578 /* see comments above. */
5579 if (tm->tm_year > 0)
5580 intresult = (tm->tm_year + 999) / 1000;
5581 else
5582 intresult = -((999 - (tm->tm_year - 1)) / 1000);
5583 break;
5584
5585 case DTK_JULIAN:
5586 if (retnumeric)
5587 PG_RETURN_NUMERIC(numeric_add_opt_error(int64_to_numeric(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)),
5588 numeric_div_opt_error(int64_to_numeric(((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * INT64CONST(1000000) + fsec),
5589 int64_to_numeric(SECS_PER_DAY * INT64CONST(1000000)),
5590 NULL),
5591 NULL));
5592 else
5593 PG_RETURN_FLOAT8(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
5594 ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) +
5595 tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY);
5596 break;
5597
5598 case DTK_ISOYEAR:
5599 intresult = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
5600 /* Adjust BC years */
5601 if (intresult <= 0)
5602 intresult -= 1;
5603 break;
5604
5605 case DTK_DOW:
5606 case DTK_ISODOW:
5607 intresult = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
5608 if (val == DTK_ISODOW && intresult == 0)
5609 intresult = 7;
5610 break;
5611
5612 case DTK_DOY:
5613 intresult = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)
5614 - date2j(tm->tm_year, 1, 1) + 1);
5615 break;
5616
5617 case DTK_TZ:
5618 case DTK_TZ_MINUTE:
5619 case DTK_TZ_HOUR:
5620 default:
5621 ereport(ERROR,
5622 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5623 errmsg("unit \"%s\" not supported for type %s",
5624 lowunits, format_type_be(TIMESTAMPOID))));
5625 intresult = 0;
5626 }
5627 }
5628 else if (type == RESERV)
5629 {
5630 switch (val)
5631 {
5632 case DTK_EPOCH:
5633 epoch = SetEpochTimestamp();
5634 /* (timestamp - epoch) / 1000000 */
5635 if (retnumeric)
5636 {
5637 Numeric result;
5638
5639 if (timestamp < (PG_INT64_MAX + epoch))
5640 result = int64_div_fast_to_numeric(timestamp - epoch, 6);
5641 else
5642 {
5643 result = numeric_div_opt_error(numeric_sub_opt_error(int64_to_numeric(timestamp),
5644 int64_to_numeric(epoch),
5645 NULL),
5646 int64_to_numeric(1000000),
5647 NULL);
5648 result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
5649 NumericGetDatum(result),
5650 Int32GetDatum(6)));
5651 }
5652 PG_RETURN_NUMERIC(result);
5653 }
5654 else
5655 {
5656 float8 result;
5657
5658 /* try to avoid precision loss in subtraction */
5659 if (timestamp < (PG_INT64_MAX + epoch))
5660 result = (timestamp - epoch) / 1000000.0;
5661 else
5662 result = ((float8) timestamp - epoch) / 1000000.0;
5663 PG_RETURN_FLOAT8(result);
5664 }
5665 break;
5666
5667 default:
5668 ereport(ERROR,
5669 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5670 errmsg("unit \"%s\" not supported for type %s",
5671 lowunits, format_type_be(TIMESTAMPOID))));
5672 intresult = 0;
5673 }
5674 }
5675 else
5676 {
5677 ereport(ERROR,
5678 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5679 errmsg("unit \"%s\" not recognized for type %s",
5680 lowunits, format_type_be(TIMESTAMPOID))));
5681 intresult = 0;
5682 }
5683
5684 if (retnumeric)
5685 PG_RETURN_NUMERIC(int64_to_numeric(intresult));
5686 else
5687 PG_RETURN_FLOAT8(intresult);
5688 }
5689
5690 Datum
5691 timestamp_part(PG_FUNCTION_ARGS)
5692 {
5693 return timestamp_part_common(fcinfo, false);
5694 }
5695
5696 Datum
5697 extract_timestamp(PG_FUNCTION_ARGS)
5698 {
5699 return timestamp_part_common(fcinfo, true);
5700 }
5701
5702 /* timestamptz_part() and extract_timestamptz()
5703 * Extract specified field from timestamp with time zone.
5704 */
5705 static Datum
5706 timestamptz_part_common(PG_FUNCTION_ARGS, bool retnumeric)
5707 {
5708 text *units = PG_GETARG_TEXT_PP(0);
5709 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
5710 int64 intresult;
5711 Timestamp epoch;
5712 int tz;
5713 int type,
5714 val;
5715 char *lowunits;
5716 fsec_t fsec;
5717 struct pg_tm tt,
5718 *tm = &tt;
5719
5720 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
5721 VARSIZE_ANY_EXHDR(units),
5722 false);
5723
5724 type = DecodeUnits(0, lowunits, &val);
5725 if (type == UNKNOWN_FIELD)
5726 type = DecodeSpecial(0, lowunits, &val);
5727
5728 if (TIMESTAMP_NOT_FINITE(timestamp))
5729 {
5730 double r = NonFiniteTimestampTzPart(type, val, lowunits,
5731 TIMESTAMP_IS_NOBEGIN(timestamp),
5732 true);
5733
5734 if (r != 0.0)
5735 {
5736 if (retnumeric)
5737 {
5738 if (r < 0)
5739 return DirectFunctionCall3(numeric_in,
5740 CStringGetDatum("-Infinity"),
5741 ObjectIdGetDatum(InvalidOid),
5742 Int32GetDatum(-1));
5743 else if (r > 0)
5744 return DirectFunctionCall3(numeric_in,
5745 CStringGetDatum("Infinity"),
5746 ObjectIdGetDatum(InvalidOid),
5747 Int32GetDatum(-1));
5748 }
5749 else
5750 PG_RETURN_FLOAT8(r);
5751 }
5752 else
5753 PG_RETURN_NULL();
5754 }
5755
5756 if (type == UNITS)
5757 {
5758 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
5759 ereport(ERROR,
5760 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
5761 errmsg("timestamp out of range")));
5762
5763 switch (val)
5764 {
5765 case DTK_TZ:
5766 intresult = -tz;
5767 break;
5768
5769 case DTK_TZ_MINUTE:
5770 intresult = (-tz / SECS_PER_MINUTE) % MINS_PER_HOUR;
5771 break;
5772
5773 case DTK_TZ_HOUR:
5774 intresult = -tz / SECS_PER_HOUR;
5775 break;
5776
5777 case DTK_MICROSEC:
5778 intresult = tm->tm_sec * INT64CONST(1000000) + fsec;
5779 break;
5780
5781 case DTK_MILLISEC:
5782 if (retnumeric)
5783 /*---
5784 * tm->tm_sec * 1000 + fsec / 1000
5785 * = (tm->tm_sec * 1'000'000 + fsec) / 1000
5786 */
5787 PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 3));
5788 else
5789 PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + fsec / 1000.0);
5790 break;
5791
5792 case DTK_SECOND:
5793 if (retnumeric)
5794 /*---
5795 * tm->tm_sec + fsec / 1'000'000
5796 * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
5797 */
5798 PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + fsec, 6));
5799 else
5800 PG_RETURN_FLOAT8(tm->tm_sec + fsec / 1000000.0);
5801 break;
5802
5803 case DTK_MINUTE:
5804 intresult = tm->tm_min;
5805 break;
5806
5807 case DTK_HOUR:
5808 intresult = tm->tm_hour;
5809 break;
5810
5811 case DTK_DAY:
5812 intresult = tm->tm_mday;
5813 break;
5814
5815 case DTK_MONTH:
5816 intresult = tm->tm_mon;
5817 break;
5818
5819 case DTK_QUARTER:
5820 intresult = (tm->tm_mon - 1) / 3 + 1;
5821 break;
5822
5823 case DTK_WEEK:
5824 intresult = date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday);
5825 break;
5826
5827 case DTK_YEAR:
5828 if (tm->tm_year > 0)
5829 intresult = tm->tm_year;
5830 else
5831 /* there is no year 0, just 1 BC and 1 AD */
5832 intresult = tm->tm_year - 1;
5833 break;
5834
5835 case DTK_DECADE:
5836 /* see comments in timestamp_part */
5837 if (tm->tm_year > 0)
5838 intresult = tm->tm_year / 10;
5839 else
5840 intresult = -((8 - (tm->tm_year - 1)) / 10);
5841 break;
5842
5843 case DTK_CENTURY:
5844 /* see comments in timestamp_part */
5845 if (tm->tm_year > 0)
5846 intresult = (tm->tm_year + 99) / 100;
5847 else
5848 intresult = -((99 - (tm->tm_year - 1)) / 100);
5849 break;
5850
5851 case DTK_MILLENNIUM:
5852 /* see comments in timestamp_part */
5853 if (tm->tm_year > 0)
5854 intresult = (tm->tm_year + 999) / 1000;
5855 else
5856 intresult = -((999 - (tm->tm_year - 1)) / 1000);
5857 break;
5858
5859 case DTK_JULIAN:
5860 if (retnumeric)
5861 PG_RETURN_NUMERIC(numeric_add_opt_error(int64_to_numeric(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)),
5862 numeric_div_opt_error(int64_to_numeric(((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * INT64CONST(1000000) + fsec),
5863 int64_to_numeric(SECS_PER_DAY * INT64CONST(1000000)),
5864 NULL),
5865 NULL));
5866 else
5867 PG_RETURN_FLOAT8(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) +
5868 ((((tm->tm_hour * MINS_PER_HOUR) + tm->tm_min) * SECS_PER_MINUTE) +
5869 tm->tm_sec + (fsec / 1000000.0)) / (double) SECS_PER_DAY);
5870 break;
5871
5872 case DTK_ISOYEAR:
5873 intresult = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
5874 /* Adjust BC years */
5875 if (intresult <= 0)
5876 intresult -= 1;
5877 break;
5878
5879 case DTK_DOW:
5880 case DTK_ISODOW:
5881 intresult = j2day(date2j(tm->tm_year, tm->tm_mon, tm->tm_mday));
5882 if (val == DTK_ISODOW && intresult == 0)
5883 intresult = 7;
5884 break;
5885
5886 case DTK_DOY:
5887 intresult = (date2j(tm->tm_year, tm->tm_mon, tm->tm_mday)
5888 - date2j(tm->tm_year, 1, 1) + 1);
5889 break;
5890
5891 default:
5892 ereport(ERROR,
5893 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5894 errmsg("unit \"%s\" not supported for type %s",
5895 lowunits, format_type_be(TIMESTAMPTZOID))));
5896 intresult = 0;
5897 }
5898 }
5899 else if (type == RESERV)
5900 {
5901 switch (val)
5902 {
5903 case DTK_EPOCH:
5904 epoch = SetEpochTimestamp();
5905 /* (timestamp - epoch) / 1000000 */
5906 if (retnumeric)
5907 {
5908 Numeric result;
5909
5910 if (timestamp < (PG_INT64_MAX + epoch))
5911 result = int64_div_fast_to_numeric(timestamp - epoch, 6);
5912 else
5913 {
5914 result = numeric_div_opt_error(numeric_sub_opt_error(int64_to_numeric(timestamp),
5915 int64_to_numeric(epoch),
5916 NULL),
5917 int64_to_numeric(1000000),
5918 NULL);
5919 result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
5920 NumericGetDatum(result),
5921 Int32GetDatum(6)));
5922 }
5923 PG_RETURN_NUMERIC(result);
5924 }
5925 else
5926 {
5927 float8 result;
5928
5929 /* try to avoid precision loss in subtraction */
5930 if (timestamp < (PG_INT64_MAX + epoch))
5931 result = (timestamp - epoch) / 1000000.0;
5932 else
5933 result = ((float8) timestamp - epoch) / 1000000.0;
5934 PG_RETURN_FLOAT8(result);
5935 }
5936 break;
5937
5938 default:
5939 ereport(ERROR,
5940 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
5941 errmsg("unit \"%s\" not supported for type %s",
5942 lowunits, format_type_be(TIMESTAMPTZOID))));
5943 intresult = 0;
5944 }
5945 }
5946 else
5947 {
5948 ereport(ERROR,
5949 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5950 errmsg("unit \"%s\" not recognized for type %s",
5951 lowunits, format_type_be(TIMESTAMPTZOID))));
5952
5953 intresult = 0;
5954 }
5955
5956 if (retnumeric)
5957 PG_RETURN_NUMERIC(int64_to_numeric(intresult));
5958 else
5959 PG_RETURN_FLOAT8(intresult);
5960 }
5961
5962 Datum
5963 timestamptz_part(PG_FUNCTION_ARGS)
5964 {
5965 return timestamptz_part_common(fcinfo, false);
5966 }
5967
5968 Datum
5969 extract_timestamptz(PG_FUNCTION_ARGS)
5970 {
5971 return timestamptz_part_common(fcinfo, true);
5972 }
5973
5974 /*
5975 * NonFiniteIntervalPart
5976 *
5977 * Used by interval_part when extracting from infinite interval. Returns
5978 * +/-Infinity if that is the appropriate result, otherwise returns zero
5979 * (which should be taken as meaning to return NULL).
5980 *
5981 * Errors thrown here for invalid units should exactly match those that
5982 * would be thrown in the calling functions, else there will be unexpected
5983 * discrepancies between finite- and infinite-input cases.
5984 */
5985 static float8
5986 NonFiniteIntervalPart(int type, int unit, char *lowunits, bool isNegative)
5987 {
5988 if ((type != UNITS) && (type != RESERV))
5989 ereport(ERROR,
5990 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5991 errmsg("unit \"%s\" not recognized for type %s",
5992 lowunits, format_type_be(INTERVALOID))));
5993
5994 switch (unit)
5995 {
5996 /* Oscillating units */
5997 case DTK_MICROSEC:
5998 case DTK_MILLISEC:
5999 case DTK_SECOND:
6000 case DTK_MINUTE:
6001 case DTK_WEEK:
6002 case DTK_MONTH:
6003 case DTK_QUARTER:
6004 return 0.0;
6005
6006 /* Monotonically-increasing units */
6007 case DTK_HOUR:
6008 case DTK_DAY:
6009 case DTK_YEAR:
6010 case DTK_DECADE:
6011 case DTK_CENTURY:
6012 case DTK_MILLENNIUM:
6013 case DTK_EPOCH:
6014 if (isNegative)
6015 return -get_float8_infinity();
6016 else
6017 return get_float8_infinity();
6018
6019 default:
6020 ereport(ERROR,
6021 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
6022 errmsg("unit \"%s\" not supported for type %s",
6023 lowunits, format_type_be(INTERVALOID))));
6024 return 0.0; /* keep compiler quiet */
6025 }
6026 }
6027
6028 /* interval_part() and extract_interval()
6029 * Extract specified field from interval.
6030 */
6031 static Datum
6032 interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
6033 {
6034 text *units = PG_GETARG_TEXT_PP(0);
6035 Interval *interval = PG_GETARG_INTERVAL_P(1);
6036 int64 intresult;
6037 int type,
6038 val;
6039 char *lowunits;
6040 struct pg_itm tt,
6041 *tm = &tt;
6042
6043 lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
6044 VARSIZE_ANY_EXHDR(units),
6045 false);
6046
6047 type = DecodeUnits(0, lowunits, &val);
6048 if (type == UNKNOWN_FIELD)
6049 type = DecodeSpecial(0, lowunits, &val);
6050
6051 if (INTERVAL_NOT_FINITE(interval))
6052 {
6053 double r = NonFiniteIntervalPart(type, val, lowunits,
6054 INTERVAL_IS_NOBEGIN(interval));
6055
6056 if (r != 0.0)
6057 {
6058 if (retnumeric)
6059 {
6060 if (r < 0)
6061 return DirectFunctionCall3(numeric_in,
6062 CStringGetDatum("-Infinity"),
6063 ObjectIdGetDatum(InvalidOid),
6064 Int32GetDatum(-1));
6065 else if (r > 0)
6066 return DirectFunctionCall3(numeric_in,
6067 CStringGetDatum("Infinity"),
6068 ObjectIdGetDatum(InvalidOid),
6069 Int32GetDatum(-1));
6070 }
6071 else
6072 PG_RETURN_FLOAT8(r);
6073 }
6074 else
6075 PG_RETURN_NULL();
6076 }
6077
6078 if (type == UNITS)
6079 {
6080 interval2itm(*interval, tm);
6081 switch (val)
6082 {
6083 case DTK_MICROSEC:
6084 intresult = tm->tm_sec * INT64CONST(1000000) + tm->tm_usec;
6085 break;
6086
6087 case DTK_MILLISEC:
6088 if (retnumeric)
6089 /*---
6090 * tm->tm_sec * 1000 + fsec / 1000
6091 * = (tm->tm_sec * 1'000'000 + fsec) / 1000
6092 */
6093 PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + tm->tm_usec, 3));
6094 else
6095 PG_RETURN_FLOAT8(tm->tm_sec * 1000.0 + tm->tm_usec / 1000.0);
6096 break;
6097
6098 case DTK_SECOND:
6099 if (retnumeric)
6100 /*---
6101 * tm->tm_sec + fsec / 1'000'000
6102 * = (tm->tm_sec * 1'000'000 + fsec) / 1'000'000
6103 */
6104 PG_RETURN_NUMERIC(int64_div_fast_to_numeric(tm->tm_sec * INT64CONST(1000000) + tm->tm_usec, 6));
6105 else
6106 PG_RETURN_FLOAT8(tm->tm_sec + tm->tm_usec / 1000000.0);
6107 break;
6108
6109 case DTK_MINUTE:
6110 intresult = tm->tm_min;
6111 break;
6112
6113 case DTK_HOUR:
6114 intresult = tm->tm_hour;
6115 break;
6116
6117 case DTK_DAY:
6118 intresult = tm->tm_mday;
6119 break;
6120
6121 case DTK_WEEK:
6122 intresult = tm->tm_mday / 7;
6123 break;
6124
6125 case DTK_MONTH:
6126 intresult = tm->tm_mon;
6127 break;
6128
6129 case DTK_QUARTER:
6130
6131 /*
6132 * We want to maintain the rule that a field extracted from a
6133 * negative interval is the negative of the field's value for
6134 * the sign-reversed interval. The broken-down tm_year and
6135 * tm_mon aren't very helpful for that, so work from
6136 * interval->month.
6137 */
6138 if (interval->month >= 0)
6139 intresult = (tm->tm_mon / 3) + 1;
6140 else
6141 intresult = -(((-interval->month % MONTHS_PER_YEAR) / 3) + 1);
6142 break;
6143
6144 case DTK_YEAR:
6145 intresult = tm->tm_year;
6146 break;
6147
6148 case DTK_DECADE:
6149 /* caution: C division may have negative remainder */
6150 intresult = tm->tm_year / 10;
6151 break;
6152
6153 case DTK_CENTURY:
6154 /* caution: C division may have negative remainder */
6155 intresult = tm->tm_year / 100;
6156 break;
6157
6158 case DTK_MILLENNIUM:
6159 /* caution: C division may have negative remainder */
6160 intresult = tm->tm_year / 1000;
6161 break;
6162
6163 default:
6164 ereport(ERROR,
6165 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
6166 errmsg("unit \"%s\" not supported for type %s",
6167 lowunits, format_type_be(INTERVALOID))));
6168 intresult = 0;
6169 }
6170 }
6171 else if (type == RESERV && val == DTK_EPOCH)
6172 {
6173 if (retnumeric)
6174 {
6175 Numeric result;
6176 int64 secs_from_day_month;
6177 int64 val;
6178
6179 /*
6180 * To do this calculation in integer arithmetic even though
6181 * DAYS_PER_YEAR is fractional, multiply everything by 4 and then
6182 * divide by 4 again at the end. This relies on DAYS_PER_YEAR
6183 * being a multiple of 0.25 and on SECS_PER_DAY being a multiple
6184 * of 4.
6185 */
6186 secs_from_day_month = ((int64) (4 * DAYS_PER_YEAR) * (interval->month / MONTHS_PER_YEAR) +
6187 (int64) (4 * DAYS_PER_MONTH) * (interval->month % MONTHS_PER_YEAR) +
6188 (int64) 4 * interval->day) * (SECS_PER_DAY / 4);
6189
6190 /*---
6191 * result = secs_from_day_month + interval->time / 1'000'000
6192 * = (secs_from_day_month * 1'000'000 + interval->time) / 1'000'000
6193 */
6194
6195 /*
6196 * Try the computation inside int64; if it overflows, do it in
6197 * numeric (slower). This overflow happens around 10^9 days, so
6198 * not common in practice.
6199 */
6200 if (!pg_mul_s64_overflow(secs_from_day_month, 1000000, &val) &&
6201 !pg_add_s64_overflow(val, interval->time, &val))
6202 result = int64_div_fast_to_numeric(val, 6);
6203 else
6204 result =
6205 numeric_add_opt_error(int64_div_fast_to_numeric(interval->time, 6),
6206 int64_to_numeric(secs_from_day_month),
6207 NULL);
6208
6209 PG_RETURN_NUMERIC(result);
6210 }
6211 else
6212 {
6213 float8 result;
6214
6215 result = interval->time / 1000000.0;
6216 result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
6217 result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
6218 result += ((double) SECS_PER_DAY) * interval->day;
6219
6220 PG_RETURN_FLOAT8(result);
6221 }
6222 }
6223 else
6224 {
6225 ereport(ERROR,
6226 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6227 errmsg("unit \"%s\" not recognized for type %s",
6228 lowunits, format_type_be(INTERVALOID))));
6229 intresult = 0;
6230 }
6231
6232 if (retnumeric)
6233 PG_RETURN_NUMERIC(int64_to_numeric(intresult));
6234 else
6235 PG_RETURN_FLOAT8(intresult);
6236 }
6237
6238 Datum
6239 interval_part(PG_FUNCTION_ARGS)
6240 {
6241 return interval_part_common(fcinfo, false);
6242 }
6243
6244 Datum
6245 extract_interval(PG_FUNCTION_ARGS)
6246 {
6247 return interval_part_common(fcinfo, true);
6248 }
6249
6250
6251 /* timestamp_zone()
6252 * Encode timestamp type with specified time zone.
6253 * This function is just timestamp2timestamptz() except instead of
6254 * shifting to the global timezone, we shift to the specified timezone.
6255 * This is different from the other AT TIME ZONE cases because instead
6256 * of shifting _to_ a new time zone, it sets the time to _be_ the
6257 * specified timezone.
6258 */
6259 Datum
6260 timestamp_zone(PG_FUNCTION_ARGS)
6261 {
6262 text *zone = PG_GETARG_TEXT_PP(0);
6263 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
6264 TimestampTz result;
6265 int tz;
6266 char tzname[TZ_STRLEN_MAX + 1];
6267 int type,
6268 val;
6269 pg_tz *tzp;
6270 struct pg_tm tm;
6271 fsec_t fsec;
6272
6273 if (TIMESTAMP_NOT_FINITE(timestamp))
6274 PG_RETURN_TIMESTAMPTZ(timestamp);
6275
6276 /*
6277 * Look up the requested timezone.
6278 */
6279 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
6280
6281 type = DecodeTimezoneName(tzname, &val, &tzp);
6282
6283 if (type == TZNAME_FIXED_OFFSET)
6284 {
6285 /* fixed-offset abbreviation */
6286 tz = val;
6287 result = dt2local(timestamp, tz);
6288 }
6289 else if (type == TZNAME_DYNTZ)
6290 {
6291 /* dynamic-offset abbreviation, resolve using specified time */
6292 if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
6293 ereport(ERROR,
6294 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6295 errmsg("timestamp out of range")));
6296 tz = -DetermineTimeZoneAbbrevOffset(&tm, tzname, tzp);
6297 result = dt2local(timestamp, tz);
6298 }
6299 else
6300 {
6301 /* full zone name, rotate to that zone */
6302 if (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0)
6303 ereport(ERROR,
6304 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6305 errmsg("timestamp out of range")));
6306 tz = DetermineTimeZoneOffset(&tm, tzp);
6307 if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
6308 ereport(ERROR,
6309 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6310 errmsg("timestamp out of range")));
6311 }
6312
6313 if (!IS_VALID_TIMESTAMP(result))
6314 ereport(ERROR,
6315 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6316 errmsg("timestamp out of range")));
6317
6318 PG_RETURN_TIMESTAMPTZ(result);
6319 }
6320
6321 /* timestamp_izone()
6322 * Encode timestamp type with specified time interval as time zone.
6323 */
6324 Datum
6325 timestamp_izone(PG_FUNCTION_ARGS)
6326 {
6327 Interval *zone = PG_GETARG_INTERVAL_P(0);
6328 Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
6329 TimestampTz result;
6330 int tz;
6331
6332 if (TIMESTAMP_NOT_FINITE(timestamp))
6333 PG_RETURN_TIMESTAMPTZ(timestamp);
6334
6335 if (INTERVAL_NOT_FINITE(zone))
6336 ereport(ERROR,
6337 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6338 errmsg("interval time zone \"%s\" must be finite",
6339 DatumGetCString(DirectFunctionCall1(interval_out,
6340 PointerGetDatum(zone))))));
6341
6342 if (zone->month != 0 || zone->day != 0)
6343 ereport(ERROR,
6344 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6345 errmsg("interval time zone \"%s\" must not include months or days",
6346 DatumGetCString(DirectFunctionCall1(interval_out,
6347 PointerGetDatum(zone))))));
6348
6349 tz = zone->time / USECS_PER_SEC;
6350
6351 result = dt2local(timestamp, tz);
6352
6353 if (!IS_VALID_TIMESTAMP(result))
6354 ereport(ERROR,
6355 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6356 errmsg("timestamp out of range")));
6357
6358 PG_RETURN_TIMESTAMPTZ(result);
6359 } /* timestamp_izone() */
6360
6361 /* TimestampTimestampTzRequiresRewrite()
6362 *
6363 * Returns false if the TimeZone GUC setting causes timestamp_timestamptz and
6364 * timestamptz_timestamp to be no-ops, where the return value has the same
6365 * bits as the argument. Since project convention is to assume a GUC changes
6366 * no more often than STABLE functions change, the answer is valid that long.
6367 */
6368 bool
6369 TimestampTimestampTzRequiresRewrite(void)
6370 {
6371 long offset;
6372
6373 if (pg_get_timezone_offset(session_timezone, &offset) && offset == 0)
6374 return false;
6375 return true;
6376 }
6377
6378 /* timestamp_timestamptz()
6379 * Convert local timestamp to timestamp at GMT
6380 */
6381 Datum
6382 timestamp_timestamptz(PG_FUNCTION_ARGS)
6383 {
6384 Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
6385
6386 PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(timestamp));
6387 }
6388
6389 /*
6390 * Convert timestamp to timestamp with time zone.
6391 *
6392 * On successful conversion, *overflow is set to zero if it's not NULL.
6393 *
6394 * If the timestamp is finite but out of the valid range for timestamptz, then:
6395 * if overflow is NULL, we throw an out-of-range error.
6396 * if overflow is not NULL, we store +1 or -1 there to indicate the sign
6397 * of the overflow, and return the appropriate timestamptz infinity.
6398 */
6399 TimestampTz
6400 timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow)
6401 {
6402 TimestampTz result;
6403 struct pg_tm tt,
6404 *tm = &tt;
6405 fsec_t fsec;
6406 int tz;
6407
6408 if (overflow)
6409 *overflow = 0;
6410
6411 if (TIMESTAMP_NOT_FINITE(timestamp))
6412 return timestamp;
6413
6414 /* We don't expect this to fail, but check it pro forma */
6415 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
6416 {
6417 tz = DetermineTimeZoneOffset(tm, session_timezone);
6418
6419 result = dt2local(timestamp, -tz);
6420
6421 if (IS_VALID_TIMESTAMP(result))
6422 {
6423 return result;
6424 }
6425 else if (overflow)
6426 {
6427 if (result < MIN_TIMESTAMP)
6428 {
6429 *overflow = -1;
6430 TIMESTAMP_NOBEGIN(result);
6431 }
6432 else
6433 {
6434 *overflow = 1;
6435 TIMESTAMP_NOEND(result);
6436 }
6437 return result;
6438 }
6439 }
6440
6441 ereport(ERROR,
6442 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6443 errmsg("timestamp out of range")));
6444
6445 return 0;
6446 }
6447
6448 /*
6449 * Promote timestamp to timestamptz, throwing error for overflow.
6450 */
6451 static TimestampTz
6452 timestamp2timestamptz(Timestamp timestamp)
6453 {
6454 return timestamp2timestamptz_opt_overflow(timestamp, NULL);
6455 }
6456
6457 /* timestamptz_timestamp()
6458 * Convert timestamp at GMT to local timestamp
6459 */
6460 Datum
6461 timestamptz_timestamp(PG_FUNCTION_ARGS)
6462 {
6463 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
6464
6465 PG_RETURN_TIMESTAMP(timestamptz2timestamp(timestamp));
6466 }
6467
6468 static Timestamp
6469 timestamptz2timestamp(TimestampTz timestamp)
6470 {
6471 Timestamp result;
6472 struct pg_tm tt,
6473 *tm = &tt;
6474 fsec_t fsec;
6475 int tz;
6476
6477 if (TIMESTAMP_NOT_FINITE(timestamp))
6478 result = timestamp;
6479 else
6480 {
6481 if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
6482 ereport(ERROR,
6483 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6484 errmsg("timestamp out of range")));
6485 if (tm2timestamp(tm, fsec, NULL, &result) != 0)
6486 ereport(ERROR,
6487 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6488 errmsg("timestamp out of range")));
6489 }
6490 return result;
6491 }
6492
6493 /* timestamptz_zone()
6494 * Evaluate timestamp with time zone type at the specified time zone.
6495 * Returns a timestamp without time zone.
6496 */
6497 Datum
6498 timestamptz_zone(PG_FUNCTION_ARGS)
6499 {
6500 text *zone = PG_GETARG_TEXT_PP(0);
6501 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
6502 Timestamp result;
6503 int tz;
6504 char tzname[TZ_STRLEN_MAX + 1];
6505 int type,
6506 val;
6507 pg_tz *tzp;
6508
6509 if (TIMESTAMP_NOT_FINITE(timestamp))
6510 PG_RETURN_TIMESTAMP(timestamp);
6511
6512 /*
6513 * Look up the requested timezone.
6514 */
6515 text_to_cstring_buffer(zone, tzname, sizeof(tzname));
6516
6517 type = DecodeTimezoneName(tzname, &val, &tzp);
6518
6519 if (type == TZNAME_FIXED_OFFSET)
6520 {
6521 /* fixed-offset abbreviation */
6522 tz = -val;
6523 result = dt2local(timestamp, tz);
6524 }
6525 else if (type == TZNAME_DYNTZ)
6526 {
6527 /* dynamic-offset abbreviation, resolve using specified time */
6528 int isdst;
6529
6530 tz = DetermineTimeZoneAbbrevOffsetTS(timestamp, tzname, tzp, &isdst);
6531 result = dt2local(timestamp, tz);
6532 }
6533 else
6534 {
6535 /* full zone name, rotate from that zone */
6536 struct pg_tm tm;
6537 fsec_t fsec;
6538
6539 if (timestamp2tm(timestamp, &tz, &tm, &fsec, NULL, tzp) != 0)
6540 ereport(ERROR,
6541 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6542 errmsg("timestamp out of range")));
6543 if (tm2timestamp(&tm, fsec, NULL, &result) != 0)
6544 ereport(ERROR,
6545 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6546 errmsg("timestamp out of range")));
6547 }
6548
6549 if (!IS_VALID_TIMESTAMP(result))
6550 ereport(ERROR,
6551 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6552 errmsg("timestamp out of range")));
6553
6554 PG_RETURN_TIMESTAMP(result);
6555 }
6556
6557 /* timestamptz_izone()
6558 * Encode timestamp with time zone type with specified time interval as time zone.
6559 * Returns a timestamp without time zone.
6560 */
6561 Datum
6562 timestamptz_izone(PG_FUNCTION_ARGS)
6563 {
6564 Interval *zone = PG_GETARG_INTERVAL_P(0);
6565 TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(1);
6566 Timestamp result;
6567 int tz;
6568
6569 if (TIMESTAMP_NOT_FINITE(timestamp))
6570 PG_RETURN_TIMESTAMP(timestamp);
6571
6572 if (INTERVAL_NOT_FINITE(zone))
6573 ereport(ERROR,
6574 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6575 errmsg("interval time zone \"%s\" must be finite",
6576 DatumGetCString(DirectFunctionCall1(interval_out,
6577 PointerGetDatum(zone))))));
6578
6579 if (zone->month != 0 || zone->day != 0)
6580 ereport(ERROR,
6581 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6582 errmsg("interval time zone \"%s\" must not include months or days",
6583 DatumGetCString(DirectFunctionCall1(interval_out,
6584 PointerGetDatum(zone))))));
6585
6586 tz = -(zone->time / USECS_PER_SEC);
6587
6588 result = dt2local(timestamp, tz);
6589
6590 if (!IS_VALID_TIMESTAMP(result))
6591 ereport(ERROR,
6592 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
6593 errmsg("timestamp out of range")));
6594
6595 PG_RETURN_TIMESTAMP(result);
6596 }
6597
6598 /* generate_series_timestamp()
6599 * Generate the set of timestamps from start to finish by step
6600 */
6601 Datum
6602 generate_series_timestamp(PG_FUNCTION_ARGS)
6603 {
6604 FuncCallContext *funcctx;
6605 generate_series_timestamp_fctx *fctx;
6606 Timestamp result;
6607
6608 /* stuff done only on the first call of the function */
6609 if (SRF_IS_FIRSTCALL())
6610 {
6611 Timestamp start = PG_GETARG_TIMESTAMP(0);
6612 Timestamp finish = PG_GETARG_TIMESTAMP(1);
6613 Interval *step = PG_GETARG_INTERVAL_P(2);
6614 MemoryContext oldcontext;
6615
6616 /* create a function context for cross-call persistence */
6617 funcctx = SRF_FIRSTCALL_INIT();
6618
6619 /*
6620 * switch to memory context appropriate for multiple function calls
6621 */
6622 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
6623
6624 /* allocate memory for user context */
6625 fctx = (generate_series_timestamp_fctx *)
6626 palloc(sizeof(generate_series_timestamp_fctx));
6627
6628 /*
6629 * Use fctx to keep state from call to call. Seed current with the
6630 * original start value
6631 */
6632 fctx->current = start;
6633 fctx->finish = finish;
6634 fctx->step = *step;
6635
6636 /* Determine sign of the interval */
6637 fctx->step_sign = interval_sign(&fctx->step);
6638
6639 if (fctx->step_sign == 0)
6640 ereport(ERROR,
6641 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6642 errmsg("step size cannot equal zero")));
6643
6644 if (INTERVAL_NOT_FINITE((&fctx->step)))
6645 ereport(ERROR,
6646 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6647 errmsg("step size cannot be infinite")));
6648
6649 funcctx->user_fctx = fctx;
6650 MemoryContextSwitchTo(oldcontext);
6651 }
6652
6653 /* stuff done on every call of the function */
6654 funcctx = SRF_PERCALL_SETUP();
6655
6656 /*
6657 * get the saved state and use current as the result for this iteration
6658 */
6659 fctx = funcctx->user_fctx;
6660 result = fctx->current;
6661
6662 if (fctx->step_sign > 0 ?
6663 timestamp_cmp_internal(result, fctx->finish) <= 0 :
6664 timestamp_cmp_internal(result, fctx->finish) >= 0)
6665 {
6666 /* increment current in preparation for next iteration */
6667 fctx->current = DatumGetTimestamp(DirectFunctionCall2(timestamp_pl_interval,
6668 TimestampGetDatum(fctx->current),
6669 PointerGetDatum(&fctx->step)));
6670
6671 /* do when there is more left to send */
6672 SRF_RETURN_NEXT(funcctx, TimestampGetDatum(result));
6673 }
6674 else
6675 {
6676 /* do when there is no more left */
6677 SRF_RETURN_DONE(funcctx);
6678 }
6679 }
6680
6681 /* generate_series_timestamptz()
6682 * Generate the set of timestamps from start to finish by step,
6683 * doing arithmetic in the specified or session timezone.
6684 */
6685 static Datum
6686 generate_series_timestamptz_internal(FunctionCallInfo fcinfo)
6687 {
6688 FuncCallContext *funcctx;
6689 generate_series_timestamptz_fctx *fctx;
6690 TimestampTz result;
6691
6692 /* stuff done only on the first call of the function */
6693 if (SRF_IS_FIRSTCALL())
6694 {
6695 TimestampTz start = PG_GETARG_TIMESTAMPTZ(0);
6696 TimestampTz finish = PG_GETARG_TIMESTAMPTZ(1);
6697 Interval *step = PG_GETARG_INTERVAL_P(2);
6698 text *zone = (PG_NARGS() == 4) ? PG_GETARG_TEXT_PP(3) : NULL;
6699 MemoryContext oldcontext;
6700
6701 /* create a function context for cross-call persistence */
6702 funcctx = SRF_FIRSTCALL_INIT();
6703
6704 /*
6705 * switch to memory context appropriate for multiple function calls
6706 */
6707 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
6708
6709 /* allocate memory for user context */
6710 fctx = (generate_series_timestamptz_fctx *)
6711 palloc(sizeof(generate_series_timestamptz_fctx));
6712
6713 /*
6714 * Use fctx to keep state from call to call. Seed current with the
6715 * original start value
6716 */
6717 fctx->current = start;
6718 fctx->finish = finish;
6719 fctx->step = *step;
6720 fctx->attimezone = zone ? lookup_timezone(zone) : session_timezone;
6721
6722 /* Determine sign of the interval */
6723 fctx->step_sign = interval_sign(&fctx->step);
6724
6725 if (fctx->step_sign == 0)
6726 ereport(ERROR,
6727 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6728 errmsg("step size cannot equal zero")));
6729
6730 if (INTERVAL_NOT_FINITE((&fctx->step)))
6731 ereport(ERROR,
6732 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
6733 errmsg("step size cannot be infinite")));
6734
6735 funcctx->user_fctx = fctx;
6736 MemoryContextSwitchTo(oldcontext);
6737 }
6738
6739 /* stuff done on every call of the function */
6740 funcctx = SRF_PERCALL_SETUP();
6741
6742 /*
6743 * get the saved state and use current as the result for this iteration
6744 */
6745 fctx = funcctx->user_fctx;
6746 result = fctx->current;
6747
6748 if (fctx->step_sign > 0 ?
6749 timestamp_cmp_internal(result, fctx->finish) <= 0 :
6750 timestamp_cmp_internal(result, fctx->finish) >= 0)
6751 {
6752 /* increment current in preparation for next iteration */
6753 fctx->current = timestamptz_pl_interval_internal(fctx->current,
6754 &fctx->step,
6755 fctx->attimezone);
6756
6757 /* do when there is more left to send */
6758 SRF_RETURN_NEXT(funcctx, TimestampTzGetDatum(result));
6759 }
6760 else
6761 {
6762 /* do when there is no more left */
6763 SRF_RETURN_DONE(funcctx);
6764 }
6765 }
6766
6767 Datum
6768 generate_series_timestamptz(PG_FUNCTION_ARGS)
6769 {
6770 return generate_series_timestamptz_internal(fcinfo);
6771 }
6772
6773 Datum
6774 generate_series_timestamptz_at_zone(PG_FUNCTION_ARGS)
6775 {
6776 return generate_series_timestamptz_internal(fcinfo);
6777 }
6778
6779 /*
6780 * Planner support function for generate_series(timestamp, timestamp, interval)
6781 */
6782 Datum
6783 generate_series_timestamp_support(PG_FUNCTION_ARGS)
6784 {
6785 Node *rawreq = (Node *) PG_GETARG_POINTER(0);
6786 Node *ret = NULL;
6787
6788 if (IsA(rawreq, SupportRequestRows))
6789 {
6790 /* Try to estimate the number of rows returned */
6791 SupportRequestRows *req = (SupportRequestRows *) rawreq;
6792
6793 if (is_funcclause(req->node)) /* be paranoid */
6794 {
6795 List *args = ((FuncExpr *) req->node)->args;
6796 Node *arg1,
6797 *arg2,
6798 *arg3;
6799
6800 /* We can use estimated argument values here */
6801 arg1 = estimate_expression_value(req->root, linitial(args));
6802 arg2 = estimate_expression_value(req->root, lsecond(args));
6803 arg3 = estimate_expression_value(req->root, lthird(args));
6804
6805 /*
6806 * If any argument is constant NULL, we can safely assume that
6807 * zero rows are returned. Otherwise, if they're all non-NULL
6808 * constants, we can calculate the number of rows that will be
6809 * returned.
6810 */
6811 if ((IsA(arg1, Const) && ((Const *) arg1)->constisnull) ||
6812 (IsA(arg2, Const) && ((Const *) arg2)->constisnull) ||
6813 (IsA(arg3, Const) && ((Const *) arg3)->constisnull))
6814 {
6815 req->rows = 0;
6816 ret = (Node *) req;
6817 }
6818 else if (IsA(arg1, Const) && IsA(arg2, Const) && IsA(arg3, Const))
6819 {
6820 Timestamp start,
6821 finish;
6822 Interval *step;
6823 Datum diff;
6824 double dstep;
6825 int64 dummy;
6826
6827 start = DatumGetTimestamp(((Const *) arg1)->constvalue);
6828 finish = DatumGetTimestamp(((Const *) arg2)->constvalue);
6829 step = DatumGetIntervalP(((Const *) arg3)->constvalue);
6830
6831 /*
6832 * Perform some prechecks which could cause timestamp_mi to
6833 * raise an ERROR. It's much better to just return some
6834 * default estimate than error out in a support function.
6835 */
6836 if (!TIMESTAMP_NOT_FINITE(start) && !TIMESTAMP_NOT_FINITE(finish) &&
6837 !pg_sub_s64_overflow(finish, start, &dummy))
6838 {
6839 diff = DirectFunctionCall2(timestamp_mi,
6840 TimestampGetDatum(finish),
6841 TimestampGetDatum(start));
6842
6843 #define INTERVAL_TO_MICROSECONDS(i) ((((double) (i)->month * DAYS_PER_MONTH + (i)->day)) * USECS_PER_DAY + (i)->time)
6844
6845 dstep = INTERVAL_TO_MICROSECONDS(step);
6846
6847 /* This equation works for either sign of step */
6848 if (dstep != 0.0)
6849 {
6850 Interval *idiff = DatumGetIntervalP(diff);
6851 double ddiff = INTERVAL_TO_MICROSECONDS(idiff);
6852
6853 req->rows = floor(ddiff / dstep + 1.0);
6854 ret = (Node *) req;
6855 }
6856 #undef INTERVAL_TO_MICROSECONDS
6857 }
6858 }
6859 }
6860 }
6861
6862 PG_RETURN_POINTER(ret);
6863 }
6864
6865
6866 /* timestamp_at_local()
6867 * timestamptz_at_local()
6868 *
6869 * The regression tests do not like two functions with the same proargs and
6870 * prosrc but different proname, but the grammar for AT LOCAL needs an
6871 * overloaded name to handle both types of timestamp, so we make simple
6872 * wrappers for it.
6873 */
6874 Datum
6875 timestamp_at_local(PG_FUNCTION_ARGS)
6876 {
6877 return timestamp_timestamptz(fcinfo);
6878 }
6879
6880 Datum
6881 timestamptz_at_local(PG_FUNCTION_ARGS)
6882 {
6883 return timestamptz_timestamp(fcinfo);
6884 }
Postgresql Clone from git://git.postgresql.org/git/postgresql.git