Skip to content

Commit 547df0c

Browse files
author
Thomas G. Lockhart
committed
Support alternate storage scheme of 64-bit integer for date/time types.
 Use "--enable-integer-datetimes" in configuration to use this rather than the original float8 storage. I would recommend the integer-based storage for any platform on which it is available. We perhaps should make this the default for the production release. Change timezone(timestamptz) results to return timestamp rather than a character string. Formerly, we didn't have a way to represent timestamps with an explicit time zone other than freezing the info into a string. Now, we can reasonably omit the explicit time zone from the result and return a timestamp with values appropriate for the specified time zone. Much cleaner, and if you need the time zone in the result you can put it into a character string pretty easily anyway. Allow fractional seconds in date/time types even for dates prior to 1BC. Limit timestamp data types to 6 decimal places of precision. Just right for a micro-second storage of int8 date/time types, and reduces the number of places ad-hoc rounding was occuring for the float8-based types. Use lookup tables for precision/rounding calculations for timestamp and interval types. Formerly used pow() to calculate the desired value but with a more limited range there is no reason to not type in a lookup table. Should be *much* better performance, though formerly there were some optimizations to help minimize the number of times pow() was called. Define a HAVE_INT64_TIMESTAMP variable. Based on the configure option "--enable-integer-datetimes" and the existing internal INT64_IS_BUSTED. Add explicit date/interval operators and functions for addition and subtraction. Formerly relied on implicit type promotion from date to timestamp with time zone. Change timezone conversion functions for the timetz type from "timetz()" to "timezone()". This is consistant with other time zone coersion functions for other types. Bump the catalog version to 200204201. Fix up regression tests to reflect changes in fractional seconds representation for date/times in BC eras. All regression tests pass on my Linux box.
1 parent 3fab493 commit 547df0c

File tree

21 files changed

+1653
-550
lines changed

21 files changed

+1653
-550
lines changed

‎src/backend/utils/adt/date.c

Lines changed: 457 additions & 53 deletions
Large diffs are not rendered by default.

‎src/backend/utils/adt/datetime.c

Lines changed: 371 additions & 185 deletions
Large diffs are not rendered by default.

‎src/backend/utils/adt/formatting.c

Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/* -----------------------------------------------------------------------
22
* formatting.c
33
*
4-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v 1.52 2002/04/03 05:39:29 petere Exp $
4+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v 1.53 2002/04/21 19:48:12 thomas Exp $
55
*
66
*
77
* Portions Copyright (c) 1999-2000, PostgreSQL Global Development Group
@@ -410,7 +410,7 @@ typedef struct
410410
typedef struct TmToChar
411411
{
412412
struct tm tm; /* classic 'tm' struct */
413-
double fsec; /* milliseconds */
413+
fsec_t fsec; /* fractional seconds */
414414
char *tzn; /* timezone */
415415
} TmToChar;
416416

@@ -1831,7 +1831,11 @@ dch_time(int arg, char *inout, int suf, int flag, FormatNode *node, void *data)
18311831
case DCH_MS: /* millisecond */
18321832
if (flag == TO_CHAR)
18331833
{
1834+
#ifdef HAVE_INT64_TIMESTAMP
1835+
sprintf(inout, "%03d", (int) (tmtc->fsec / INT64CONST(1000)));
1836+
#else
18341837
sprintf(inout, "%03d", (int) rint(tmtc->fsec * 1000));
1838+
#endif
18351839
if (S_THth(suf))
18361840
str_numth(p_inout, inout, S_TH_TYPE(suf));
18371841
if (S_THth(suf))
@@ -1874,7 +1878,11 @@ dch_time(int arg, char *inout, int suf, int flag, FormatNode *node, void *data)
18741878
case DCH_US: /* microsecond */
18751879
if (flag == TO_CHAR)
18761880
{
1881+
#ifdef HAVE_INT64_TIMESTAMP
1882+
sprintf(inout, "%06d", (int) tmtc->fsec);
1883+
#else
18771884
sprintf(inout, "%06d", (int) rint(tmtc->fsec * 1000000));
1885+
#endif
18781886
if (S_THth(suf))
18791887
str_numth(p_inout, inout, S_TH_TYPE(suf));
18801888
if (S_THth(suf))
@@ -2868,7 +2876,7 @@ to_timestamp(PG_FUNCTION_ARGS)
28682876
date_len,
28692877
tz = 0;
28702878
struct tm tm;
2871-
double fsec = 0;
2879+
fsec_t fsec = 0;
28722880

28732881
ZERO_tm(&tm);
28742882
ZERO_tmfc(&tmfc);
@@ -3071,10 +3079,17 @@ to_timestamp(PG_FUNCTION_ARGS)
30713079
tm.tm_yday - y[i - 1];
30723080
}
30733081

3082+
#ifdef HAVE_INT64_TIMESTAMP
3083+
if (tmfc.ms)
3084+
fsec += tmfc.ms * 1000;
3085+
if (tmfc.us)
3086+
fsec += tmfc.us;
3087+
#else
30743088
if (tmfc.ms)
30753089
fsec += (double) tmfc.ms / 1000;
30763090
if (tmfc.us)
30773091
fsec += (double) tmfc.us / 1000000;
3092+
#endif
30783093

30793094
/* -------------------------------------------------------------- */
30803095

‎src/backend/utils/adt/int8.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/int8.c,v 1.37 2002/02/23 01:01:30 momjian Exp $
10+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/int8.c,v 1.38 2002/04/21 19:48:12 thomas Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -23,6 +23,7 @@
2323

2424
/* this should be set in pg_config.h, but just in case it wasn't: */
2525
#ifndef INT64_FORMAT
26+
#warning "Broken pg_config.h should have defined INT64_FORMAT"
2627
#define INT64_FORMAT "%ld"
2728
#endif
2829

‎src/backend/utils/adt/nabstime.c

Lines changed: 49 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/nabstime.c,v 1.92 2002/03/06 06:10:13 momjian Exp $
12+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/nabstime.c,v 1.93 2002/04/21 19:48:12 thomas Exp $
1313
*
1414
* NOTES
1515
*
@@ -76,34 +76,13 @@
7676
AbsoluteTimeGetDatum(t1), \
7777
AbsoluteTimeGetDatum(t2))) ? (t2) : (t1))
7878

79-
#ifdef NOT_USED
80-
static char *unit_tab[] = {
81-
"second", "seconds", "minute", "minutes",
82-
"hour", "hours", "day", "days", "week", "weeks",
83-
"month", "months", "year", "years"};
84-
85-
#define UNITMAXLEN 7 /* max length of a unit name */
86-
#define NUNITS 14 /* number of different units */
87-
88-
/* table of seconds per unit (month = 30 days, year = 365 days) */
89-
static int sec_tab[] = {
90-
1, 1, 60, 60,
91-
3600, 3600, 86400, 86400, 604800, 604800,
92-
2592000, 2592000, 31536000, 31536000};
93-
#endif
9479

9580
/*
9681
* Function prototypes -- internal to this file only
9782
*/
9883

9984
static AbsoluteTime tm2abstime(struct tm * tm, int tz);
10085
static void reltime2tm(RelativeTime time, struct tm * tm);
101-
102-
#ifdef NOT_USED
103-
static int correct_unit(char *unit, int *unptr);
104-
static int correct_dir(char *direction, int *signptr);
105-
#endif
106-
10786
static int istinterval(char *i_string,
10887
AbsoluteTime *i_start,
10988
AbsoluteTime *i_end);
@@ -177,7 +156,7 @@ GetCurrentAbsoluteTime(void)
177156
} /* GetCurrentAbsoluteTime() */
178157

179158

180-
/* GetCurrentAbsoluteTime()
159+
/* GetCurrentAbsoluteTimeUsec()
181160
* Get the current system time. Set timezone parameters if not specified elsewhere.
182161
* Define HasCTZSet to allow clients to specify the default timezone.
183162
*
@@ -271,13 +250,17 @@ GetCurrentTime(struct tm * tm)
271250

272251

273252
void
274-
GetCurrentTimeUsec(struct tm * tm, double *fsec)
253+
GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec)
275254
{
276255
int tz;
277256
int usec;
278257

279258
abstime2tm(GetCurrentTransactionStartTimeUsec(&usec), &tz, tm, NULL);
259+
#ifdef HAVE_INT64_TIMESTAMP
260+
*fsec = usec;
261+
#else
280262
*fsec = usec * 1.0e-6;
263+
#endif
281264

282265
return;
283266
} /* GetCurrentTimeUsec() */
@@ -493,7 +476,7 @@ nabstimein(PG_FUNCTION_ARGS)
493476
{
494477
char *str = PG_GETARG_CSTRING(0);
495478
AbsoluteTime result;
496-
double fsec;
479+
fsec_t fsec;
497480
int tz = 0;
498481
struct tm date,
499482
*tm = &date;
@@ -713,7 +696,7 @@ timestamp_abstime(PG_FUNCTION_ARGS)
713696
{
714697
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
715698
AbsoluteTime result;
716-
double fsec;
699+
fsec_t fsec;
717700
int tz;
718701
struct tm tt,
719702
*tm = &tt;
@@ -767,7 +750,9 @@ abstime_timestamp(PG_FUNCTION_ARGS)
767750

768751
default:
769752
abstime2tm(abstime, &tz, tm, &tzn);
770-
result = abstime + ((date2j(1970, 1, 1) - date2j(2000, 1, 1)) * 86400) + tz;
753+
if (tm2timestamp(tm, 0, NULL, &result) != 0)
754+
elog(ERROR, "Unable convert ABSTIME to TIMESTAMP"
755+
"\n\tabstime_timestamp() internal error");
771756
break;
772757
};
773758

@@ -783,7 +768,7 @@ timestamptz_abstime(PG_FUNCTION_ARGS)
783768
{
784769
TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
785770
AbsoluteTime result;
786-
double fsec;
771+
fsec_t fsec;
787772
struct tm tt,
788773
*tm = &tt;
789774

@@ -810,6 +795,11 @@ abstime_timestamptz(PG_FUNCTION_ARGS)
810795
{
811796
AbsoluteTime abstime = PG_GETARG_ABSOLUTETIME(0);
812797
TimestampTz result;
798+
struct tm tt,
799+
*tm = &tt;
800+
int tz;
801+
char zone[MAXDATELEN + 1],
802+
*tzn = zone;
813803

814804
switch (abstime)
815805
{
@@ -827,7 +817,10 @@ abstime_timestamptz(PG_FUNCTION_ARGS)
827817
break;
828818

829819
default:
830-
result = abstime + ((date2j(1970, 1, 1) - date2j(2000, 1, 1)) * 86400);
820+
abstime2tm(abstime, &tz, tm, &tzn);
821+
if (tm2timestamp(tm, 0, &tz, &result) != 0)
822+
elog(ERROR, "Unable convert ABSTIME to TIMESTAMP WITH TIME ZONE"
823+
"\n\tabstime_timestamp() internal error");
831824
break;
832825
};
833826

@@ -849,7 +842,7 @@ reltimein(PG_FUNCTION_ARGS)
849842
RelativeTime result;
850843
struct tm tt,
851844
*tm = &tt;
852-
double fsec;
845+
fsec_t fsec;
853846
int dtype;
854847
char *field[MAXDATEFIELDS];
855848
int nf,
@@ -860,14 +853,14 @@ reltimein(PG_FUNCTION_ARGS)
860853
elog(ERROR, "Bad (length) reltime external representation '%s'", str);
861854

862855
if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
863-
|| (DecodeDateDelta(field, ftype, nf, &dtype, tm, &fsec) != 0))
856+
|| (DecodeInterval(field, ftype, nf, &dtype, tm, &fsec) != 0))
864857
elog(ERROR, "Bad reltime external representation '%s'", str);
865858

866859
switch (dtype)
867860
{
868861
case DTK_DELTA:
869862
result = ((((tm->tm_hour * 60) + tm->tm_min) * 60) + tm->tm_sec);
870-
result += (((tm->tm_year * 365) + (tm->tm_mon * 30) + tm->tm_mday) * (24 * 60 * 60));
863+
result += ((tm->tm_year * 36525 * 864) + (((tm->tm_mon * 30) + tm->tm_mday) * 86400));
871864
break;
872865

873866
default:
@@ -893,7 +886,7 @@ reltimeout(PG_FUNCTION_ARGS)
893886
char buf[MAXDATELEN + 1];
894887

895888
reltime2tm(time, tm);
896-
EncodeTimeSpan(tm, 0, DateStyle, buf);
889+
EncodeInterval(tm, 0, DateStyle, buf);
897890

898891
result = pstrdup(buf);
899892
PG_RETURN_CSTRING(result);
@@ -903,7 +896,7 @@ reltimeout(PG_FUNCTION_ARGS)
903896
static void
904897
reltime2tm(RelativeTime time, struct tm * tm)
905898
{
906-
TMODULO(time, tm->tm_year, 31536000);
899+
TMODULO(time, tm->tm_year, 31557600);
907900
TMODULO(time, tm->tm_mon, 2592000);
908901
TMODULO(time, tm->tm_mday, 86400);
909902
TMODULO(time, tm->tm_hour, 3600);
@@ -988,7 +981,11 @@ interval_reltime(PG_FUNCTION_ARGS)
988981
RelativeTime time;
989982
int year,
990983
month;
984+
#ifdef HAVE_INT64_TIMESTAMP
985+
int64 span;
986+
#else
991987
double span;
988+
#endif
992989

993990
if (interval->month == 0)
994991
{
@@ -1006,7 +1003,13 @@ interval_reltime(PG_FUNCTION_ARGS)
10061003
month = interval->month;
10071004
}
10081005

1009-
span = (((((double) 365 * year) + ((double) 30 * month)) * 86400) + interval->time);
1006+
#ifdef HAVE_INT64_TIMESTAMP
1007+
span = ((((INT64CONST(365250000) * year) + (INT64CONST(30000000) * month))
1008+
* INT64CONST(86400)) + interval->time);
1009+
span /= INT64CONST(1000000);
1010+
#else
1011+
span = (((((double) 365.25 * year) + ((double) 30 * month)) * 86400) + interval->time);
1012+
#endif
10101013

10111014
if ((span < INT_MIN) || (span > INT_MAX))
10121015
time = INVALID_RELTIME;
@@ -1036,10 +1039,19 @@ reltime_interval(PG_FUNCTION_ARGS)
10361039
break;
10371040

10381041
default:
1039-
TMODULO(reltime, year, 31536000);
1040-
TMODULO(reltime, month, 2592000);
1042+
#ifdef HAVE_INT64_TIMESTAMP
1043+
year = (reltime / (36525 * 864));
1044+
reltime -= (year * (36525 * 864));
1045+
month = (reltime / (30 * 86400));
1046+
reltime -= (month * (30 * 86400));
1047+
1048+
result->time = (reltime * INT64CONST(1000000));
1049+
#else
1050+
TMODULO(reltime, year, (36525 * 864));
1051+
TMODULO(reltime, month, (30 * 86400));
10411052

10421053
result->time = reltime;
1054+
#endif
10431055
result->month = ((12 * year) + month);
10441056
break;
10451057
}
@@ -1090,11 +1102,6 @@ timepl(PG_FUNCTION_ARGS)
10901102
AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
10911103
RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
10921104

1093-
#if 0
1094-
if (t1 == CURRENT_ABSTIME)
1095-
t1 = GetCurrentTransactionStartTime();
1096-
#endif
1097-
10981105
if (AbsoluteTimeIsReal(t1) &&
10991106
RelativeTimeIsValid(t2) &&
11001107
((t2 > 0) ? (t1 < NOEND_ABSTIME - t2)
@@ -1114,11 +1121,6 @@ timemi(PG_FUNCTION_ARGS)
11141121
AbsoluteTime t1 = PG_GETARG_ABSOLUTETIME(0);
11151122
RelativeTime t2 = PG_GETARG_RELATIVETIME(1);
11161123

1117-
#if 0
1118-
if (t1 == CURRENT_ABSTIME)
1119-
t1 = GetCurrentTransactionStartTime();
1120-
#endif
1121-
11221124
if (AbsoluteTimeIsReal(t1) &&
11231125
RelativeTimeIsValid(t2) &&
11241126
((t2 > 0) ? (t1 > NOSTART_ABSTIME + t2)

‎src/backend/utils/adt/selfuncs.c

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
*
1616
*
1717
* IDENTIFICATION
18-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.108 2002/04/16 23:08:11 tgl Exp $
18+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v 1.109 2002/04/21 19:48:13 thomas Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -2427,17 +2427,30 @@ convert_timevalue_to_scalar(Datum value, Oid typid)
24272427
* assumed average month length of 365.25/12.0 days. Not
24282428
* too accurate, but plenty good enough for our purposes.
24292429
*/
2430+
#ifdef HAVE_INT64_TIMESTAMP
2431+
return (interval->time + (interval->month * ((365.25 / 12.0) * 86400000000.0)));
2432+
#else
24302433
return interval->time +
24312434
interval->month * (365.25 / 12.0 * 24.0 * 60.0 * 60.0);
2435+
#endif
24322436
}
24332437
case RELTIMEOID:
2438+
#ifdef HAVE_INT64_TIMESTAMP
2439+
return (DatumGetRelativeTime(value) * 1000000.0);
2440+
#else
24342441
return DatumGetRelativeTime(value);
2442+
#endif
24352443
case TINTERVALOID:
24362444
{
24372445
TimeInterval interval = DatumGetTimeInterval(value);
24382446

2447+
#ifdef HAVE_INT64_TIMESTAMP
2448+
if (interval->status != 0)
2449+
return ((interval->data[1] - interval->data[0]) * 1000000.0);
2450+
#else
24392451
if (interval->status != 0)
24402452
return interval->data[1] - interval->data[0];
2453+
#endif
24412454
return 0; /* for lack of a better idea */
24422455
}
24432456
case TIMEOID:
@@ -2447,7 +2460,11 @@ convert_timevalue_to_scalar(Datum value, Oid typid)
24472460
TimeTzADT *timetz = DatumGetTimeTzADTP(value);
24482461

24492462
/* use GMT-equivalent time */
2463+
#ifdef HAVE_INT64_TIMESTAMP
2464+
return (double) (timetz->time + (timetz->zone * 1000000.0));
2465+
#else
24502466
return (double) (timetz->time + timetz->zone);
2467+
#endif
24512468
}
24522469
}
24532470

0 commit comments

Comments
 (0)