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