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