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