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