diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index ee1c957..a30f852 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -6669,6 +6669,38 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); + make_date + + make_date(year int, month int, + day int) + + date + + Create date from year, month and day fields + + make_date(2013, 7, 15) + 2013-07-15 + + + + + + make_time + + make_time(hour int, min int, + sec double precision) + + time + + Create time from hour, minutes and second fields + + make_time(8, 15, 23.5) + 08:15:23.5 + + + + + now now() diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index 8677520..7e33014 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -2729,3 +2729,72 @@ timetz_izone(PG_FUNCTION_ARGS) PG_RETURN_TIMETZADT_P(result); } + +/* + * make_date() + * date constructor + */ +Datum +make_date(PG_FUNCTION_ARGS) +{ + struct pg_tm tm; + DateADT date; + int dterr; + + tm.tm_year = PG_GETARG_INT32(0); + tm.tm_mon = PG_GETARG_INT32(1); + tm.tm_mday = PG_GETARG_INT32(2); + + dterr = ValidateDate(DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY), + true, false, false, &tm); + + if (dterr != 0) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW), + errmsg("date field value out of range: \"%d-%d-%d\"", + tm.tm_year, tm.tm_mon, tm.tm_mday))); + + if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday)) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), + errmsg("date out of range: \"%d-%d-%d\"", tm.tm_year, tm.tm_mon, tm.tm_mday))); + + date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE; + + PG_RETURN_DATEADT(date); +} + +/* + * make_time() + * time constructor + */ +Datum +make_time(PG_FUNCTION_ARGS) +{ + int tm_hour = PG_GETARG_INT32(0); + int tm_min = PG_GETARG_INT32(1); + float8 sec = PG_GETARG_FLOAT8(2); + TimeADT time; + + if (tm_hour < 0 || tm_min < 0 || tm_min > MINS_PER_HOUR - 1 || + sec < 0.0 || sec > (float8) SECS_PER_MINUTE || + tm_hour > HOURS_PER_DAY || + /* test for > 24:00:00 */ + (tm_hour == HOURS_PER_DAY && + (tm_min > 0 || sec > 0.0))) + ereport(ERROR, + (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW), + errmsg("time field value out of range: \"%02d:%02d:%0*.*f\"", + tm_hour, tm_min, + MAX_TIME_PRECISION + 3, + MAX_TIME_PRECISION, fabs(sec)))); + +#ifdef HAVE_INT64_TIMESTAMP + time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) * USECS_PER_SEC + + rint(sec * USECS_PER_SEC); +#else + time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec +#endif + + PG_RETURN_TIMEADT(time); +} diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index 7a08b92..e9e905a 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -44,8 +44,6 @@ static int DecodeTimezone(char *str, int *tzp); static const datetkn *datebsearch(const char *key, const datetkn *base, int nel); static int DecodeDate(char *str, int fmask, int *tmask, bool *is2digits, struct pg_tm * tm); -static int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc, - struct pg_tm * tm); static void TrimTrailingZeros(char *str); static void AppendSeconds(char *cp, int sec, fsec_t fsec, int precision, bool fillzeros); @@ -2266,7 +2264,7 @@ DecodeDate(char *str, int fmask, int *tmask, bool *is2digits, * Check valid year/month/day values, handle BC and DOY cases * Return 0 if okay, a DTERR code if not. */ -static int +int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc, struct pg_tm * tm) { diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index f03dd0b..c085cde 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4650,6 +4650,12 @@ DESCR("int8range constructor"); DATA(insert OID = 3946 ( int8range PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 3926 "20 20 25" _null_ _null_ _null_ _null_ range_constructor3 _null_ _null_ _null_ )); DESCR("int8range constructor"); +/* date, time constructors */ +DATA(insert OID = 3968 ( make_date PGNSP PGUID 12 1 0 0 0 f f f f t f s 3 0 1082 "23 23 23" _null_ _null_ "{year,month,day}" _null_ make_date _null_ _null_ _null_ )); +DESCR("construct date"); +DATA(insert OID = 3969 ( make_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 3 0 1083 "23 23 701" _null_ _null_ "{hour,min,sec}" _null_ make_time _null_ _null_ _null_ )); +DESCR("construct time"); + /* spgist support functions */ DATA(insert OID = 4001 ( spggettuple PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 16 "2281 2281" _null_ _null_ _null_ _null_ spggettuple _null_ _null_ _null_ )); DESCR("spgist(internal)"); diff --git a/src/include/utils/date.h b/src/include/utils/date.h index 7c3a1be..7febb7e 100644 --- a/src/include/utils/date.h +++ b/src/include/utils/date.h @@ -204,4 +204,7 @@ extern Datum timetz_izone(PG_FUNCTION_ARGS); extern Datum timetz_pl_interval(PG_FUNCTION_ARGS); extern Datum timetz_mi_interval(PG_FUNCTION_ARGS); +extern Datum make_date(PG_FUNCTION_ARGS); +extern Datum make_time(PG_FUNCTION_ARGS); + #endif /* DATE_H */ diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h index 3cd921a..4b39ecc 100644 --- a/src/include/utils/datetime.h +++ b/src/include/utils/datetime.h @@ -310,4 +310,7 @@ extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl); extern Datum pg_timezone_abbrevs(PG_FUNCTION_ARGS); extern Datum pg_timezone_names(PG_FUNCTION_ARGS); +extern int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc, struct pg_tm *tm); + + #endif /* DATETIME_H */ diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out index b603745..ac38aab 100644 --- a/src/test/regress/expected/date.out +++ b/src/test/regress/expected/date.out @@ -1184,3 +1184,25 @@ select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today' f | f | t (1 row) +-- test constructors +select make_date(2013, 7, 15); + make_date +------------ + 07-15-2013 +(1 row) + +select make_time(8, 20, 0.0); + make_time +----------- + 08:20:00 +(1 row) + +-- should fail +select make_date(2013, 2, 30); +ERROR: date field value out of range: "2013-2-30" +select make_date(2013, 13, 1); +ERROR: date field value out of range: "2013-13-1" +select make_time(10, 55, 100.1); +ERROR: time field value out of range: "10:55:100.100000" +select make_time(24, 0, 2.1); +ERROR: time field value out of range: "24:00:02.100000" diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql index d179ddf..0f5ef96 100644 --- a/src/test/regress/sql/date.sql +++ b/src/test/regress/sql/date.sql @@ -276,3 +276,14 @@ select 'infinity'::date, '-infinity'::date; select 'infinity'::date > 'today'::date as t; select '-infinity'::date < 'today'::date as t; select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date); + + +-- test constructors +select make_date(2013, 7, 15); +select make_time(8, 20, 0.0); + +-- should fail +select make_date(2013, 2, 30); +select make_date(2013, 13, 1); +select make_time(10, 55, 100.1); +select make_time(24, 0, 2.1);