granicus.if.org Git - postgresql/blob - src/backend/utils/adt/timestamp.c

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