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