Hello
updated version - a precheck is very simple, and I what I tested it is
enough
Regards
Pavel
2014-02-28 15:11 GMT+01:00 Alvaro Herrera <alvhe...@2ndquadrant.com>:
> Pavel Stehule escribió:
>
> > so still I prefer to allow numeric time zones.
> >
> > What I can:
> >
> > a) disallow numeric only timezone without prefix "+" or "-"
> >
> > or
> >
> > b) add "+" prefix to time zone, when number is possitive.
> >
> > I prefer @a.
>
> I can live with (a) too. But I wonder if we should restrict the allowed
> tz even further, for example to say that there must always be either 2
> digits (no colon) or 4 digits, with or without a colon.
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
commit a9acac784c64fdba144ea0ae3a817fbc8cf4fa45
Author: Pavel Stehule <pavel.steh...@gooddata.com>
Date: Sun Mar 2 10:55:37 2014 +0100
fix make_timestamptz
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ff50328..ce6d00e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6723,6 +6723,32 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
<row>
<entry>
<indexterm>
+ <primary>make_interval</primary>
+ </indexterm>
+ <literal>
+ <function>
+ make_interval(<parameter>years</parameter> <type>int</type> DEFAULT 0,
+ <parameter>months</parameter> <type>int</type> DEFAULT 0,
+ <parameter>weeks</parameter> <type>int</type> DEFAULT 0,
+ <parameter>days</parameter> <type>int</type> DEFAULT 0,
+ <parameter>hours</parameter> <type>int</type> DEFAULT 0,
+ <parameter>mins</parameter> <type>int</type> DEFAULT 0,
+ <parameter>secs</parameter> <type>double precision</type> DEFAULT 0.0)
+ </function>
+ </literal>
+ </entry>
+ <entry><type>interval</type></entry>
+ <entry>
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields
+ </entry>
+ <entry><literal>make_interval(days := 10)</literal></entry>
+ <entry><literal>10 days</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>make_time</primary>
</indexterm>
<literal>
@@ -6744,6 +6770,57 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
<row>
<entry>
<indexterm>
+ <primary>make_timestamp</primary>
+ </indexterm>
+ <literal>
+ <function>
+ make_timestamp(<parameter>year</parameter> <type>int</type>,
+ <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>,
+ <parameter>hour</parameter> <type>int</type>,
+ <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)
+ </function>
+ </literal>
+ </entry>
+ <entry><type>timestamp</type></entry>
+ <entry>
+ Create timestamp from year, month, day, hour, minute and seconds fields
+ </entry>
+ <entry><literal>make_timestamp(1-23, 7, 15, 8, 15, 23.5)</literal></entry>
+ <entry><literal>2013-07-15 08:15:23.5</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_timestamptz</primary>
+ </indexterm>
+ <literal>
+ <function>
+ make_timestamptz(<parameter>year</parameter> <type>int</type>,
+ <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>,
+ <parameter>hour</parameter> <type>int</type>,
+ <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>,
+ <optional> <parameter>timezone</parameter> <type>text</type> </optional>)
+ </function>
+ </literal>
+ </entry>
+ <entry><type>timestamp with time zone</type></entry>
+ <entry>
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields. When <parameter>timezone</parameter> is not specified,
+ then current time zone is used.
+ </entry>
+ <entry><literal>make_timestamp(1-23, 7, 15, 8, 15, 23.5)</literal></entry>
+ <entry><literal>2013-07-15 08:15:23.5+01</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>now</primary>
</indexterm>
<literal><function>now()</function></literal>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 04dfbb0..59a6f85 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -822,3 +822,9 @@ CREATE OR REPLACE FUNCTION
CREATE OR REPLACE FUNCTION
json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset';
+
+CREATE OR REPLACE FUNCTION
+ make_interval(years int4 DEFAULT 0, months int4 DEFAULT 0, weeks int4 DEFAULT 0,
+ days int4 DEFAULT 0, hours int4 DEFAULT 0, mins int4 DEFAULT 0,
+ secs double precision DEFAULT 0.0)
+ RETURNS interval STRICT IMMUTABLE LANGUAGE internal AS 'make_interval';
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 06cc0cd..404cc79 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -1106,6 +1106,7 @@ time_in(PG_FUNCTION_ARGS)
static int
tm2time(struct pg_tm * tm, fsec_t fsec, TimeADT *result)
{
+ /* this should match make_time_internal and make_timestamp_internal */
#ifdef HAVE_INT64_TIMESTAMP
*result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec)
* USECS_PER_SEC) + fsec;
@@ -1244,14 +1245,11 @@ timetypmodout(PG_FUNCTION_ARGS)
}
/*
- * make_time - time constructor
+ * time constructor used for make_time and make_timetz
*/
-Datum
-make_time(PG_FUNCTION_ARGS)
+static TimeADT
+make_time_internal(int tm_hour, int tm_min, double sec)
{
- int tm_hour = PG_GETARG_INT32(0);
- int tm_min = PG_GETARG_INT32(1);
- double sec = PG_GETARG_FLOAT8(2);
TimeADT time;
/* This should match the checks in DecodeTimeOnly */
@@ -1273,9 +1271,24 @@ make_time(PG_FUNCTION_ARGS)
time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec;
#endif
- PG_RETURN_TIMEADT(time);
+ return time;
}
+/*
+ * make_time - time constructor
+ */
+Datum
+make_time(PG_FUNCTION_ARGS)
+{
+ int tm_hour = PG_GETARG_INT32(0);
+ int tm_min = PG_GETARG_INT32(1);
+ double sec = PG_GETARG_FLOAT8(2);
+ TimeADT time;
+
+ time = make_time_internal(tm_hour, tm_min, sec);
+
+ PG_RETURN_TIMEADT(time);
+}
/* time_transform()
* Flatten calls to time_scale() and timetz_scale() that solely represent
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 0d32428..2b72bd1 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -40,7 +40,6 @@ static int DecodeNumberField(int len, char *str,
struct pg_tm * tm, fsec_t *fsec, bool *is2digits);
static int DecodeTime(char *str, int fmask, int range,
int *tmask, struct pg_tm * tm, fsec_t *fsec);
-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);
@@ -2075,6 +2074,10 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
else if (mer == PM && tm->tm_hour != HOURS_PER_DAY / 2)
tm->tm_hour += HOURS_PER_DAY / 2;
+ /*
+ * This should match the checks in make_time_internal and
+ * make_timestamp_internal
+ */
if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > MINS_PER_HOUR - 1 ||
tm->tm_sec < 0 || tm->tm_sec > SECS_PER_MINUTE ||
tm->tm_hour > HOURS_PER_DAY ||
@@ -2707,7 +2710,7 @@ DecodeNumberField(int len, char *str, int fmask,
*
* NB: this must *not* ereport on failure; see commands/variable.c.
*/
-static int
+int
DecodeTimezone(char *str, int *tzp)
{
int tz;
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index cf6982b..8456d08 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -483,6 +483,211 @@ timestamptz_in(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMPTZ(result);
}
+/*
+ * make_timestamp_internal
+ * workhorse for make_timestamp and make_timestamptz
+ */
+static Timestamp
+make_timestamp_internal(int year, int month, int day,
+ int hour, int min, double sec)
+{
+ struct pg_tm tm;
+ TimeOffset date;
+ TimeOffset time;
+ int dterr;
+ Timestamp result;
+
+ tm.tm_year = year;
+ tm.tm_mon = month;
+ tm.tm_mday = day;
+
+ /*
+ * Note: we'll reject zero or negative year values. Perhaps negatives
+ * should be allowed to represent BC years?
+ */
+ dterr = ValidateDate(DTK_DATE_M, false, false, false, &tm);
+
+ if (dterr != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("date field value out of range: %d-%02d-%02d",
+ year, month, day)));
+
+ 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-%02d-%02d",
+ year, month, day)));
+
+ date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
+
+ /* This should match the checks in DecodeTimeOnly */
+ if (hour < 0 || min < 0 || min > MINS_PER_HOUR - 1 ||
+ sec < 0 || sec > SECS_PER_MINUTE ||
+ hour > HOURS_PER_DAY ||
+ /* test for > 24:00:00 */
+ (hour == HOURS_PER_DAY && (min > 0 || sec > 0)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("time field value out of range: %d:%02d:%02g",
+ hour, min, sec)));
+
+ /* This should match tm2time */
+#ifdef HAVE_INT64_TIMESTAMP
+ time = (((hour * MINS_PER_HOUR + min) * SECS_PER_MINUTE)
+ * USECS_PER_SEC) + rint(sec * USECS_PER_SEC);
+
+ result = date * USECS_PER_DAY + time;
+ /* check for major overflow */
+ if ((result - time) / USECS_PER_DAY != date)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
+ year, month, day,
+ hour, min, sec)));
+
+ /* check for just-barely overflow (okay except time-of-day wraps) */
+ /* caution: we want to allow 1999-12-31 24:00:00 */
+ if ((result < 0 && date > 0) ||
+ (result > 0 && date < -1))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range: %d-%02d-%02d %d:%02d:%02g",
+ year, month, day,
+ hour, min, sec)));
+#else
+ time = ((hour * MINS_PER_HOUR + min) * SECS_PER_MINUTE) + sec;
+ result = date * SECS_PER_DAY + time;
+#endif
+
+ return result;
+}
+
+/*
+ * make_timestamp() - timestamp constructor
+ */
+Datum
+make_timestamp(PG_FUNCTION_ARGS)
+{
+ int32 year = PG_GETARG_INT32(0);
+ int32 month = PG_GETARG_INT32(1);
+ int32 mday = PG_GETARG_INT32(2);
+ int32 hour = PG_GETARG_INT32(3);
+ int32 min = PG_GETARG_INT32(4);
+ float8 sec = PG_GETARG_FLOAT8(5);
+ Timestamp result;
+
+ result = make_timestamp_internal(year, month, mday,
+ hour, min, sec);
+
+ PG_RETURN_TIMESTAMP(result);
+}
+
+/*
+ * make_timestamptz() - timestamp with time zone constructor
+ */
+Datum
+make_timestamptz(PG_FUNCTION_ARGS)
+{
+ int32 year = PG_GETARG_INT32(0);
+ int32 month = PG_GETARG_INT32(1);
+ int32 mday = PG_GETARG_INT32(2);
+ int32 hour = PG_GETARG_INT32(3);
+ int32 min = PG_GETARG_INT32(4);
+ float8 sec = PG_GETARG_FLOAT8(5);
+ Timestamp result;
+
+ result = make_timestamp_internal(year, month, mday,
+ hour, min, sec);
+
+ PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(result));
+}
+
+Datum
+make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
+{
+ int32 year = PG_GETARG_INT32(0);
+ int32 month = PG_GETARG_INT32(1);
+ int32 mday = PG_GETARG_INT32(2);
+ int32 hour = PG_GETARG_INT32(3);
+ int32 min = PG_GETARG_INT32(4);
+ float8 sec = PG_GETARG_FLOAT8(5);
+ text *zone = PG_GETARG_TEXT_PP(6);
+ Timestamp timestamp;
+ char tzstr[TZ_STRLEN_MAX + 1];
+ char *tzstr_ptr;
+ int tz;
+ struct pg_tm tt,
+ *tm = &tt;
+ fsec_t fsec;
+
+ timestamp = make_timestamp_internal(year, month, mday,
+ hour, min, sec);
+
+ if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ /*
+ * Try to decode the user-supplied timezone. If it can be parsed as a
+ * numeric timezone specification, use that; otherwise try to match it
+ * to the timezone token tables via DecodeSpecial.
+ */
+ text_to_cstring_buffer(zone, tzstr, sizeof(tzstr));
+
+ /*
+ * DecodeTimezone should to process numeric timezones and fails on non
+ * numeric timezones. But, because pg_tzset doesn't fail on numeric
+ * timezones (expect syntax check by parser inside input function),
+ * simple precheck is necessary here too.
+ */
+ tzstr_ptr = tzstr;
+
+ /* skip leading whitespace */
+ while (*tzstr_ptr != '\0' && isspace((unsigned char) *tzstr_ptr))
+ tzstr_ptr++;
+
+ if (isdigit(*tzstr_ptr))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid input syntax for time zone: \"%s\"", tzstr_ptr),
+ errhint("Numeric time zone require \"-\" or \"+\" as first char.")));
+
+ if (DecodeTimezone(tzstr_ptr, &tz) != 0)
+ {
+ char *lowzone;
+ int type,
+ val;
+
+ lowzone = downcase_truncate_identifier(tzstr_ptr,
+ strlen(tzstr_ptr),
+ false);
+ type = DecodeSpecial(0, lowzone, &val);
+
+ if (type == TZ || type == DTZ)
+ tz = val * MINS_PER_HOUR;
+ else
+ {
+ pg_tz *tzp;
+
+ tzp = pg_tzset(tzstr_ptr);
+
+ if (tzp)
+ tz = DetermineTimeZoneOffset(tm, tzp);
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("time zone \"%s\" not recognized", tzstr_ptr)));
+ tz = 0; /* keep compiler quiet */
+ }
+ }
+ }
+
+ PG_RETURN_TIMESTAMPTZ((TimestampTz) dt2local(timestamp, -tz));
+}
+
/* timestamptz_out()
* Convert a timestamp to external form.
*/
@@ -1220,6 +1425,40 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
}
}
+/*
+ * make_interval - numeric Interval constructor
+ */
+Datum
+make_interval(PG_FUNCTION_ARGS)
+{
+ Interval *result;
+
+ int32 years = PG_GETARG_INT32(0);
+ int32 months = PG_GETARG_INT32(1);
+ int32 weeks = PG_GETARG_INT32(2);
+ int32 days = PG_GETARG_INT32(3);
+ int32 hours = PG_GETARG_INT32(4);
+ int32 mins= PG_GETARG_INT32(5);
+ double secs = PG_GETARG_FLOAT8(6);
+
+ result = (Interval *) palloc(sizeof(Interval));
+ result->month = years * MONTHS_PER_YEAR + months;
+ result->day = weeks * 7 + days;
+
+#ifdef HAVE_INT64_TIMESTAMP
+ result->time = (((((hours * INT64CONST(60)) +
+ mins) * INT64CONST(60)) +
+ secs) * USECS_PER_SEC);
+#else
+ result->time = (((hours * (double) MINS_PER_HOUR) +
+ mins) * (double) SECS_PER_MINUTE) +
+ secs;
+#endif
+
+ AdjustIntervalForTypmod(result, -1);
+
+ PG_RETURN_INTERVAL_P(result);
+}
/* EncodeSpecialTimestamp()
* Convert reserved timestamp data type to string.
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index e33670c..f5912d2 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4718,11 +4718,19 @@ 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 */
+/* date, time, timestamp constructors */
DATA(insert OID = 3846 ( make_date PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1082 "23 23 23" _null_ _null_ "{year,month,day}" _null_ make_date _null_ _null_ _null_ ));
DESCR("construct date");
DATA(insert OID = 3847 ( make_time PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1083 "23 23 701" _null_ _null_ "{hour,min,sec}" _null_ make_time _null_ _null_ _null_ ));
DESCR("construct time");
+DATA(insert OID = 3782 ( make_timestamp PGNSP PGUID 12 1 0 0 0 f f f f t f i 6 0 1114 "23 23 23 23 23 701" _null_ _null_ "{year,month,mday,hour,min,sec}" _null_ make_timestamp _null_ _null_ _null_ ));
+DESCR("construct timestamp");
+DATA(insert OID = 3783 ( make_timestamptz PGNSP PGUID 12 1 0 0 0 f f f f t f s 6 0 1184 "23 23 23 23 23 701" _null_ _null_ "{year,month,mday,hour,min,sec}" _null_ make_timestamptz _null_ _null_ _null_ ));
+DESCR("construct timestamp with time zone");
+DATA(insert OID = 3784 ( make_timestamptz PGNSP PGUID 12 1 0 0 0 f f f f t f s 7 0 1184 "23 23 23 23 23 701 25" _null_ _null_ "{year,month,mday,hour,min,sec,timezone}" _null_ make_timestamptz_at_timezone _null_ _null_ _null_ ));
+DESCR("construct timestamp with time zone");
+DATA(insert OID = 3785 ( make_interval PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 1186 "23 23 23 23 23 23 701" _null_ _null_ "{years,months,weeks,days,hours,mins,secs}" _null_ make_interval _null_ _null_ _null_ ));
+DESCR("construct interval");
/* 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_ ));
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index b106482..fc3a1f6 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -283,6 +283,7 @@ extern int ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
extern int DecodeDateTime(char **field, int *ftype,
int nf, int *dtype,
struct pg_tm * tm, fsec_t *fsec, int *tzp);
+extern int DecodeTimezone(char *str, int *tzp);
extern int DecodeTimeOnly(char **field, int *ftype,
int nf, int *dtype,
struct pg_tm * tm, fsec_t *fsec, int *tzp);
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index 47fb866..2731c6a 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -121,6 +121,10 @@ extern Datum timestamp_gt_timestamptz(PG_FUNCTION_ARGS);
extern Datum timestamp_ge_timestamptz(PG_FUNCTION_ARGS);
extern Datum timestamp_cmp_timestamptz(PG_FUNCTION_ARGS);
+extern Datum make_timestamp(PG_FUNCTION_ARGS);
+extern Datum make_timestamptz(PG_FUNCTION_ARGS);
+extern Datum make_timestamptz_at_timezone(PG_FUNCTION_ARGS);
+
extern Datum timestamptz_eq_timestamp(PG_FUNCTION_ARGS);
extern Datum timestamptz_ne_timestamp(PG_FUNCTION_ARGS);
extern Datum timestamptz_lt_timestamp(PG_FUNCTION_ARGS);
@@ -151,6 +155,7 @@ extern Datum interval_larger(PG_FUNCTION_ARGS);
extern Datum interval_justify_interval(PG_FUNCTION_ARGS);
extern Datum interval_justify_hours(PG_FUNCTION_ARGS);
extern Datum interval_justify_days(PG_FUNCTION_ARGS);
+extern Datum make_interval(PG_FUNCTION_ARGS);
extern Datum timestamp_trunc(PG_FUNCTION_ARGS);
extern Datum interval_trunc(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 99fd0ca..b5a92b1 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -807,3 +807,34 @@ select interval_hash('30 days'::interval) = interval_hash('1 month'::interval) a
t
(1 row)
+-- numeric constructor
+select make_interval(years := 2);
+ make_interval
+---------------
+ @ 2 years
+(1 row)
+
+select make_interval(years := 1, months := 6);
+ make_interval
+-----------------
+ @ 1 year 6 mons
+(1 row)
+
+select make_interval(weeks := 3);
+ make_interval
+---------------
+ @ 21 days
+(1 row)
+
+select make_interval(days := 10);
+ make_interval
+---------------
+ @ 10 days
+(1 row)
+
+select make_interval(hours := 2, mins := 10, secs := 25.3);
+ make_interval
+-----------------------------
+ @ 2 hours 10 mins 25.3 secs
+(1 row)
+
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index db2cfe6..a092fc2 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -1585,3 +1585,10 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
| 2001 1 1 1 1 1 1
(65 rows)
+-- timestamp numeric fields constructor
+SELECT make_timestamp(2014,12,28,6,30,45.887);
+ make_timestamp
+------------------------------
+ Sun Dec 28 06:30:45.887 2014
+(1 row)
+
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 9f4f7a4..9ce66b8 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1697,3 +1697,58 @@ SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
--Cleanup
DROP TABLE TIMESTAMPTZ_TST;
+-- timestamp numeric constructor
+SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
+ make_timestamptz
+---------------------------------
+ Sun Jul 15 08:15:55.33 1973 PDT
+(1 row)
+
+SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2');
+ make_timestamptz
+---------------------------------
+ Sat Jul 14 23:15:55.33 1973 PDT
+(1 row)
+
+-- should fail
+SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '2');
+ERROR: invalid input syntax for time zone: "2"
+HINT: Numeric time zone require "-" or "+" as first char.
+-- should be true
+SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz;
+ ?column?
+----------
+ t
+(1 row)
+
+-- timestamp with full time zone name
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'Europe/Moscow') = '2014-12-10 10:10:10 Europe/Moscow'::timestamptz;
+ ?column?
+----------
+ t
+(1 row)
+
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'Europe/London') = '2014-12-10 10:10:10 Europe/London'::timestamptz;
+ ?column?
+----------
+ t
+(1 row)
+
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'Europe/Prague') = '2014-12-10 10:10:10 Europe/Prague'::timestamptz;
+ ?column?
+----------
+ t
+(1 row)
+
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'Australia/Sydney') = '2014-12-10 10:10:10 Australia/Sydney'::timestamptz;
+ ?column?
+----------
+ t
+(1 row)
+
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'America/Vancouver') = '2014-12-10 10:10:10 America/Vancouver'::timestamptz;
+ ?column?
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 7cee286..0e73bf7 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -261,3 +261,10 @@ select interval '0:0:0.7', interval '@ 0.70 secs', interval '0.7 seconds';
-- check that '30 days' equals '1 month' according to the hash function
select '30 days'::interval = '1 month'::interval as t;
select interval_hash('30 days'::interval) = interval_hash('1 month'::interval) as t;
+
+-- numeric constructor
+select make_interval(years := 2);
+select make_interval(years := 1, months := 6);
+select make_interval(weeks := 3);
+select make_interval(days := 10);
+select make_interval(hours := 2, mins := 10, secs := 25.3);
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index c4ed4ee..b22cd48 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -222,3 +222,6 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
FROM TIMESTAMP_TBL;
+
+-- timestamp numeric fields constructor
+SELECT make_timestamp(2014,12,28,6,30,45.887);
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 4eef62e..35b2b75 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -254,3 +254,20 @@ INSERT INTO TIMESTAMPTZ_TST VALUES(4, '1000000312 23:58:48 IST');
SELECT * FROM TIMESTAMPTZ_TST ORDER BY a;
--Cleanup
DROP TABLE TIMESTAMPTZ_TST;
+
+-- timestamp numeric constructor
+SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
+SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2');
+
+-- should fail
+SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '2');
+
+-- should be true
+SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, '+2') = '1973-07-15 08:15:55.33+02'::timestamptz;
+
+-- timestamp with full time zone name
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'Europe/Moscow') = '2014-12-10 10:10:10 Europe/Moscow'::timestamptz;
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'Europe/London') = '2014-12-10 10:10:10 Europe/London'::timestamptz;
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'Europe/Prague') = '2014-12-10 10:10:10 Europe/Prague'::timestamptz;
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'Australia/Sydney') = '2014-12-10 10:10:10 Australia/Sydney'::timestamptz;
+select make_timestamptz(2014, 12, 10, 10, 10, 10, 'America/Vancouver') = '2014-12-10 10:10:10 America/Vancouver'::timestamptz;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
