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