Hello updated patch
time zone type functions are overloaded now postgres=# select '1973-07-15 08:15:55.33+02'::timestamptz; timestamptz --------------------------- 1973-07-15 07:15:55.33+01 (1 row) postgres=# SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, 2); make_timestamptz --------------------------- 1973-07-15 07:15:55.33+01 (1 row) postgres=# SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33); make_timestamptz --------------------------- 1973-07-15 08:15:55.33+01 (1 row) Regards Pavel 2013/12/15 Pavel Stehule <pavel.steh...@gmail.com> > Hello > > > 2013/12/13 Jim Nasby <j...@nasby.net> > >> On 12/13/13 1:49 PM, Fabrízio de Royes Mello wrote: >> >>> >>> On Fri, Dec 13, 2013 at 5:35 PM, Tom Lane <t...@sss.pgh.pa.us <mailto: >>> t...@sss.pgh.pa.us>> wrote: >>> >>> > >>> > =?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= >>> <fabriziome...@gmail.com<mailto: >>> fabriziome...@gmail.com>> writes: >>> > > I think the goal of the "make_date/time/timestamp" function series >>> is build >>> > > a date/time/timestamp from scratch, so the use of >>> 'make_timestamptz' is to >>> > > build a specific timestamp with timezone and don't convert it. >>> > >>> > Yeah; we don't really want to incur an extra timezone rotation just >>> to get >>> > to a timestamptz. However, it's not clear to me if make_timestamptz() >>> > needs to have an explicit zone parameter or not. It could just assume >>> > that you meant the active timezone. >>> > >>> >>> +1. And if you want a different timezone you can just set the 'timezone' >>> GUC. >>> >> >> Why wouldn't we have a version that optionally accepts the timezone? That >> mirrors what you can currently do with a cast from text, and having to set >> the GUC if you need a different TZ would be a real PITA. >> > > It is not bad idea. > > What will be format for timezone in this case? Is a doble enough? > > last version of this patch attached (without overloading in this moment) > > > > >> -- >> Jim C. Nasby, Data Architect j...@nasby.net >> 512.569.9461 (cell) http://jim.nasby.net >> > >
commit 7f03cda7a5a5e173b51b2ddc87c1e437a5dc7b34 Author: Pavel Stehule <pavel.steh...@gooddata.com> Date: Thu Dec 12 18:08:47 2013 +0100 initial implementation make_timestamp diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index a411e3a..9adec6b 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -6735,6 +6735,78 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); <row> <entry> <indexterm> + <primary>make_timetz</primary> + </indexterm> + <literal> + <function> + make_timetz(<parameter>hour</parameter> <type>int</type>, + <parameter>min</parameter> <type>int</type>, + <parameter>sec</parameter> <type>double precision</type>, + <optional> <parameter>timezone</parameter> <type>int</type> </optional>) + </function> + </literal> + </entry> + <entry><type>time with time zone</type></entry> + <entry> + Create time with time zone from hour, minute and seconds fields + </entry> + <entry><literal>make_timetz(8, 15, 23.5)</literal></entry> + <entry><literal>08:15:23.5+01</literal></entry> + </row> + + <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>int</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 + </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/utils/adt/date.c b/src/backend/utils/adt/date.c index fe091da..08831d6 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -1246,38 +1246,95 @@ 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 hour, int 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 */ - if (tm_hour < 0 || tm_min < 0 || tm_min > MINS_PER_HOUR - 1 || + if (hour < 0 || min < 0 || min > MINS_PER_HOUR - 1 || sec < 0 || sec > SECS_PER_MINUTE || - tm_hour > HOURS_PER_DAY || + hour > HOURS_PER_DAY || /* test for > 24:00:00 */ - (tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0))) + (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", - tm_hour, tm_min, sec))); + hour, min, sec))); /* This should match tm2time */ #ifdef HAVE_INT64_TIMESTAMP - time = (((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + time = (((hour * MINS_PER_HOUR + 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; + time = ((hour * MINS_PER_HOUR + min) * SECS_PER_MINUTE) + sec; #endif + return time; +} + +/* + * make_time - time constructor + */ +Datum +make_time(PG_FUNCTION_ARGS) +{ + TimeADT time; + + time = make_time_internal(PG_GETARG_INT32(0), /* hour */ + PG_GETARG_INT32(1), /* min */ + PG_GETARG_FLOAT8(2)); /* sec */ + PG_RETURN_TIMEADT(time); } +/* + * make_timetz - timetz constructor + */ +Datum +make_timetz(PG_FUNCTION_ARGS) +{ + TimeADT time; + + time = make_time_internal(PG_GETARG_INT32(0), /* hour */ + PG_GETARG_INT32(1), /* min */ + PG_GETARG_FLOAT8(2)); /* sec */ + + PG_RETURN_DATUM(DirectFunctionCall1(time_timetz, + TimeADTGetDatum(time))); +} + +/* + * make_timetz_at_timezone - timetz constructor + */ +Datum +make_timetz_at_timezone(PG_FUNCTION_ARGS) +{ + TimeADT time; + int tz_hr; + TimeTzADT *result; + + time = make_time_internal(PG_GETARG_INT32(0), /* hour */ + PG_GETARG_INT32(1), /* min */ + PG_GETARG_FLOAT8(2)); /* sec */ + + tz_hr = PG_GETARG_INT32(3); + + if (abs(tz_hr) > MAX_TZDISP_HOUR) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TIME_ZONE_DISPLACEMENT_VALUE), + errmsg("time zone displacement out of range: \"%d\"", + tz_hr))); + + result = (TimeTzADT *) palloc(sizeof(TimeTzADT)); + + result->time = time; + result->zone = -(tz_hr * SECS_PER_HOUR); + + PG_RETURN_TIMETZADT_P(result); +} /* time_transform() * Flatten calls to time_scale() and timetz_scale() that solely represent diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index c3c71b7..dd53181 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -477,6 +477,148 @@ timestamptz_in(PG_FUNCTION_ARGS) PG_RETURN_TIMESTAMPTZ(result); } +/* + * used as workhorse of 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) +{ + Timestamp result; + + result = make_timestamp_internal(PG_GETARG_INT32(0), /* year */ + PG_GETARG_INT32(1), /* month */ + PG_GETARG_INT32(2), /* mday */ + PG_GETARG_INT32(3), /* hour */ + PG_GETARG_INT32(4), /* min */ + PG_GETARG_FLOAT8(5)); /* sec */ + + PG_RETURN_TIMESTAMP(result); +} + +/* + * make_timestamptz() - timestamp with time zone constructor + */ +Datum +make_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp result; + + result = make_timestamp_internal(PG_GETARG_INT32(0), /* year */ + PG_GETARG_INT32(1), /* month */ + PG_GETARG_INT32(2), /* mday */ + PG_GETARG_INT32(3), /* hour */ + PG_GETARG_INT32(4), /* min */ + PG_GETARG_FLOAT8(5)); /* sec */ + + PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(result)); +} + +/* + * make_timestamptz_at_timezone() - timestamp with time zone constructor + */ +Datum +make_timestamptz_at_timezone(PG_FUNCTION_ARGS) +{ + Timestamp result; + int tz_hr; + + result = make_timestamp_internal(PG_GETARG_INT32(0), /* year */ + PG_GETARG_INT32(1), /* month */ + PG_GETARG_INT32(2), /* mday */ + PG_GETARG_INT32(3), /* hour */ + PG_GETARG_INT32(4), /* min */ + PG_GETARG_FLOAT8(5)); /* sec */ + + tz_hr = PG_GETARG_INT32(6); + + if (abs(tz_hr) > MAX_TZDISP_HOUR) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TIME_ZONE_DISPLACEMENT_VALUE), + errmsg("time zone displacement out of range: \"%d\"", + tz_hr))); + + PG_RETURN_TIMESTAMPTZ((TimestampTz) dt2local(result, (tz_hr * SECS_PER_HOUR))); +} + /* timestamptz_out() * Convert a timestamp to external form. */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 0117500..1fd2a7f 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -4675,11 +4675,21 @@ 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 = 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_ )); +/* 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,mday}" _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 = 3920 ( make_timetz PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1266 "23 23 701" _null_ _null_ "{hour,min,sec}" _null_ make_timetz _null_ _null_ _null_ )); +DESCR("construct time with time zone"); +DATA(insert OID = 3921 ( make_timetz PGNSP PGUID 12 1 0 0 0 f f f f t f i 4 0 1266 "23 23 701 23" _null_ _null_ "{hour,min,sec,timezone}" _null_ make_timetz_at_timezone _null_ _null_ _null_ )); +DESCR("construct time with time zone"); +DATA(insert OID = 3931 ( 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 = 3932 ( make_timestamptz PGNSP PGUID 12 1 0 0 0 f f f f t f i 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 = 3935 ( make_timestamptz PGNSP PGUID 12 1 0 0 0 f f f f t f i 7 0 1184 "23 23 23 23 23 701 23" _null_ _null_ "{year,month,mday,hour,min,sec,timezone}" _null_ make_timestamptz_at_timezone _null_ _null_ _null_ )); +DESCR("construct timestamp with time zone"); /* 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/date.h b/src/include/utils/date.h index 83a5bea..96a25f3 100644 --- a/src/include/utils/date.h +++ b/src/include/utils/date.h @@ -156,6 +156,8 @@ extern Datum time_send(PG_FUNCTION_ARGS); extern Datum timetypmodin(PG_FUNCTION_ARGS); extern Datum timetypmodout(PG_FUNCTION_ARGS); extern Datum make_time(PG_FUNCTION_ARGS); +extern Datum make_timetz(PG_FUNCTION_ARGS); +extern Datum make_timetz_at_timezone(PG_FUNCTION_ARGS); extern Datum time_transform(PG_FUNCTION_ARGS); extern Datum time_scale(PG_FUNCTION_ARGS); extern Datum time_eq(PG_FUNCTION_ARGS); diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h index e7a53ff..aeadf74 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); 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..deb1f40 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -1697,3 +1697,23 @@ 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 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) + diff --git a/src/test/regress/expected/timetz.out b/src/test/regress/expected/timetz.out index 4391131..cb75cc6 100644 --- a/src/test/regress/expected/timetz.out +++ b/src/test/regress/expected/timetz.out @@ -93,3 +93,15 @@ ERROR: operator does not exist: time with time zone + time with time zone LINE 1: SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TI... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. +SELECT make_timetz(8, 15, 55.333); + make_timetz +----------------- + 08:15:55.333-08 +(1 row) + +SELECT make_timetz(8, 15, 55.333, 2); + make_timetz +----------------- + 08:15:55.333+02 +(1 row) + 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..a118096 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -254,3 +254,10 @@ 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 be true +SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, 2) = '1973-07-15 08:15:55.33+02'::timestamptz; diff --git a/src/test/regress/sql/timetz.sql b/src/test/regress/sql/timetz.sql index c41686a..4bbd6a8 100644 --- a/src/test/regress/sql/timetz.sql +++ b/src/test/regress/sql/timetz.sql @@ -40,3 +40,6 @@ SELECT f1 AS "Ten" FROM TIMETZ_TBL WHERE f1 >= '00:00-07'; -- where we do mixed-type arithmetic. - thomas 2000-12-02 SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TIMETZ_TBL; + +SELECT make_timetz(8, 15, 55.333); +SELECT make_timetz(8, 15, 55.333, 2);
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers