proposal: simple date constructor from numeric values
Hello
long time I am thinking about simple function for creating date or
timestamp values based on numeric types without necessity to create
format string.
some like ansi_date(year, month, day) and ansi_timestamp(year, month,
day, hour, minuts, sec, msec, offset)
What do you think about this idea?
Regards
Pavel Stehule
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello
2013/6/29 Pavel Stehule <pavel.stehule@gmail.com>:
Hello
long time I am thinking about simple function for creating date or
timestamp values based on numeric types without necessity to create
format string.some like ansi_date(year, month, day) and ansi_timestamp(year, month,
day, hour, minuts, sec, msec, offset)What do you think about this idea?
I found so same idea was discussed three years ago
/messages/by-id/14107.1276443739@sss.pgh.pa.us
and it is a part of our ToDo: "Add function to allow the creation of
timestamps using parameters"
so we can have a functions with signatures
CREATE OR REPLACE FUNCTION construct_date(year int, month int DEFAULT
1, day int DEFAULT 1) RETURNS date;
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);
CREATE OR REPLACE FUNCTION construct_timestap(year int, month int DEFAULT ....
CREATE OR REPLACE FUNCTION construct_timestamp_with_timezone(year int,
month int DEFAULT1, ...
???
Regards
Pavel Stehule
Regards
Pavel Stehule
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7/1/13 3:47 AM, Pavel Stehule wrote:
and it is a part of our ToDo: "Add function to allow the creation of
timestamps using parameters"so we can have a functions with signatures
I would just name them date(...), time(...), etc.
CREATE OR REPLACE FUNCTION construct_date(year int, month int DEFAULT
1, day int DEFAULT 1) RETURNS date;
I would not use default values for this one.
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);
If we are using integer datetime storage, we shouldn't use floats to
construct them.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2013/7/1 Peter Eisentraut <peter_e@gmx.net>:
On 7/1/13 3:47 AM, Pavel Stehule wrote:
and it is a part of our ToDo: "Add function to allow the creation of
timestamps using parameters"so we can have a functions with signatures
I would just name them date(...), time(...), etc.
+1
CREATE OR REPLACE FUNCTION construct_date(year int, month int DEFAULT
1, day int DEFAULT 1) RETURNS date;I would not use default values for this one.
I have no problem with it
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);If we are using integer datetime storage, we shouldn't use floats to
construct them.
so possible signature signature should be
CREATE FUNCTION time(hour int, mi int, sec int, used int) ??
and
CREATE FUNCTION timetz(hour int, mi int, sec int, isec int, tz int)
??
Regards
Pavel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2013/7/2 Pavel Stehule <pavel.stehule@gmail.com>:
2013/7/1 Peter Eisentraut <peter_e@gmx.net>:
On 7/1/13 3:47 AM, Pavel Stehule wrote:
and it is a part of our ToDo: "Add function to allow the creation of
timestamps using parameters"so we can have a functions with signatures
I would just name them date(...), time(...), etc.
I tested this names, and I got a syntax error for function "time"
we doesn't support real type constructors, and parser doesn't respect syntax.
so we can use a different names, or we can try to implement type
constructor functions.
Comments
Regards
Pavel
+1
CREATE OR REPLACE FUNCTION construct_date(year int, month int DEFAULT
1, day int DEFAULT 1) RETURNS date;I would not use default values for this one.
I have no problem with it
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);If we are using integer datetime storage, we shouldn't use floats to
construct them.so possible signature signature should be
CREATE FUNCTION time(hour int, mi int, sec int, used int) ??
and
CREATE FUNCTION timetz(hour int, mi int, sec int, isec int, tz int)
??
Regards
Pavel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2013/7/3 Pavel Stehule <pavel.stehule@gmail.com>:
2013/7/2 Pavel Stehule <pavel.stehule@gmail.com>:
2013/7/1 Peter Eisentraut <peter_e@gmx.net>:
On 7/1/13 3:47 AM, Pavel Stehule wrote:
and it is a part of our ToDo: "Add function to allow the creation of
timestamps using parameters"so we can have a functions with signatures
I would just name them date(...), time(...), etc.
I tested this names, and I got a syntax error for function "time"
we doesn't support real type constructors, and parser doesn't respect syntax.
so we can use a different names, or we can try to implement type
constructor functions.
constructor function - : A niladic SQL-invoked function of which
exactly one is implicitly specified for every structured type. An
invocation of the constructor function for data type
returns a value of the most specific type such that is not null ...
as minimum for Postgres - a possibility to implement function with
same name as type name.
Regards
Pavel
.
Comments
Regards
Pavel
+1
CREATE OR REPLACE FUNCTION construct_date(year int, month int DEFAULT
1, day int DEFAULT 1) RETURNS date;I would not use default values for this one.
I have no problem with it
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);If we are using integer datetime storage, we shouldn't use floats to
construct them.so possible signature signature should be
CREATE FUNCTION time(hour int, mi int, sec int, used int) ??
and
CREATE FUNCTION timetz(hour int, mi int, sec int, isec int, tz int)
??
Regards
Pavel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1 July 2013 17:47, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2013/6/29 Pavel Stehule <pavel.stehule@gmail.com>:
long time I am thinking about simple function for creating date or
timestamp values based on numeric types without necessity to create
format string.What do you think about this idea?
I found so same idea was discussed three years ago
I suggested something similar also:
/messages/by-id/AANLkTi=W1wtcL7qR4PuQaQ=UoabmjSUSz6QGJTUCXF-P@mail.gmail.com
The thread I linked died off without reaching a consensus about what
the functions ought to be named, although Josh and Robert were
generally supportive of the idea.
The function signatures I have been using in my own C functions are:
* date(year int, month int, day int) returns date
* datetime(year int, month int, day int, hour int, minute int, second
int) returns timestamp
Cheers,
BJ
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello
2013/7/3 Brendan Jurd <direvus@gmail.com>:
On 1 July 2013 17:47, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2013/6/29 Pavel Stehule <pavel.stehule@gmail.com>:
long time I am thinking about simple function for creating date or
timestamp values based on numeric types without necessity to create
format string.What do you think about this idea?
I found so same idea was discussed three years ago
I suggested something similar also:
/messages/by-id/AANLkTi=W1wtcL7qR4PuQaQ=UoabmjSUSz6QGJTUCXF-P@mail.gmail.com
The thread I linked died off without reaching a consensus about what
the functions ought to be named, although Josh and Robert were
generally supportive of the idea.The function signatures I have been using in my own C functions are:
* date(year int, month int, day int) returns date
* datetime(year int, month int, day int, hour int, minute int, second
int) returns timestamp
I am thinking so for these functions exists some consensus - minimally
for function "date"(year, month, int) - I dream about this function
ten years :)
I am not sure about "datetime":
a) we use "timestamp" name for same thing in pg
b) we can simply construct timestamp as sum of date + time, what is
little bit more practical (for me), because it doesn't use too wide
parameter list.
so my proposal is two new function "date" and "time"
but, because we doesn't support type constructor function, I don't
think so name "date" is good (in this moment)
MSSQL has function DATEFROMPARTS, TIMEFROMPARTS and DATETIMEFROMPARTS
MySQL has little bit obscure function MAKEDATE(year, dayinyear) and
MAKETIME(hour, min, sec)
Oracle and db2 has nothing similar
what do you think about names?
make_date
make_time
I don't would to use to_date, to_time functions, a) because these
functions use formatted input, b) we hold some compatibility with
Oracle.
Regards
Pavel Stehule
Cheers,
BJ
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3 July 2013 21:41, Pavel Stehule <pavel.stehule@gmail.com> wrote:
I am thinking so for these functions exists some consensus - minimally
for function "date"(year, month, int) - I dream about this function
ten years :)I am not sure about "datetime":
a) we use "timestamp" name for same thing in pg
b) we can simply construct timestamp as sum of date + time, what is
little bit more practical (for me), because it doesn't use too wide
parameter list.
I agree. I've got no issues with using date + time arithmetic to
build a timestamp.
what do you think about names?
make_date
make_time
I am fine with those names. 'make', 'construct', 'build', etc. are
all reasonable verbs for what the functions do, but 'make' is nice and
short, and will be familiar to people who've used a 'mktime'.
I don't would to use to_date, to_time functions, a) because these
functions use formatted input, b) we hold some compatibility with
Oracle.
Yes, I agree.
Cheers,
BJ
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Peter Eisentraut escribi�:
On 7/1/13 3:47 AM, Pavel Stehule wrote:
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);If we are using integer datetime storage, we shouldn't use floats to
construct them.
I think this is wrong. Datetime storage may be int, but since they're
microseconds underneath, we'd be unable to specify a full-resolution
timestamp if we didn't have float ms or integer �s. So either the
seconds argument should allow fractions (probably not a good idea), or
we should have another integer argument for microseconds (not
milliseconds as the above signature implies).
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2013/7/3 Alvaro Herrera <alvherre@2ndquadrant.com>:
Peter Eisentraut escribió:
On 7/1/13 3:47 AM, Pavel Stehule wrote:
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);If we are using integer datetime storage, we shouldn't use floats to
construct them.I think this is wrong. Datetime storage may be int, but since they're
microseconds underneath, we'd be unable to specify a full-resolution
timestamp if we didn't have float ms or integer µs. So either the
seconds argument should allow fractions (probably not a good idea), or
we should have another integer argument for microseconds (not
milliseconds as the above signature implies).
so make_time(hour int, mi int, sec int, usec int DEFAULT 0)
Is good for all ?
Regards
Pavel
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
Peter Eisentraut escribi�:
On 7/1/13 3:47 AM, Pavel Stehule wrote:
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);If we are using integer datetime storage, we shouldn't use floats to
construct them.
I think this is wrong. Datetime storage may be int, but since they're
microseconds underneath, we'd be unable to specify a full-resolution
timestamp if we didn't have float ms or integer �s. So either the
seconds argument should allow fractions (probably not a good idea), or
we should have another integer argument for microseconds (not
milliseconds as the above signature implies).
FWIW, I'd vote for allowing the seconds to be fractional. That's the
way the user perceives things:
regression=# select '12:34:56.789'::time;
time
--------------
12:34:56.789
(1 row)
Moreover, an integer microseconds argument would be a shortsighted idea
because it wires the precision limit into the function API. As long as
we make the seconds argument be float8, it will work fine even when the
underlying precision switches to, say, nanoseconds.
And lastly, those default arguments are a bad idea as well. There's no
reasonable use-case for make_time(12); that's almost certainly an error.
Even more so for make_time(). While you could make some case for
make_time(12,34) being useful, I don't think it buys much compared
to writing out make_time(12,34,0), and having just one function
signature is that much less cognitive load on users.
So my vote is for make_time(hour int, min int, sec float8).
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2013/7/3 Tom Lane <tgl@sss.pgh.pa.us>:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
Peter Eisentraut escribió:
On 7/1/13 3:47 AM, Pavel Stehule wrote:
CREATE OR REPLACE FUNCTION construct_time(hour int DEFAULT 0, mi int
DEFAULT 0, sec int DEFAULT 0, ms float DEFAULT 0.0);If we are using integer datetime storage, we shouldn't use floats to
construct them.I think this is wrong. Datetime storage may be int, but since they're
microseconds underneath, we'd be unable to specify a full-resolution
timestamp if we didn't have float ms or integer ľs. So either the
seconds argument should allow fractions (probably not a good idea), or
we should have another integer argument for microseconds (not
milliseconds as the above signature implies).FWIW, I'd vote for allowing the seconds to be fractional. That's the
way the user perceives things:regression=# select '12:34:56.789'::time;
time
--------------
12:34:56.789
(1 row)Moreover, an integer microseconds argument would be a shortsighted idea
because it wires the precision limit into the function API. As long as
we make the seconds argument be float8, it will work fine even when the
underlying precision switches to, say, nanoseconds.And lastly, those default arguments are a bad idea as well. There's no
reasonable use-case for make_time(12); that's almost certainly an error.
Even more so for make_time(). While you could make some case for
make_time(12,34) being useful, I don't think it buys much compared
to writing out make_time(12,34,0), and having just one function
signature is that much less cognitive load on users.
I had a plan use DEFAULT only for usec parameter (if it was used).
Seconds was mandatory parameter.
After tests on prototype I think so fractional sec is better. Separate
value (in usec) is really big number - not practical for hand writing
So my vote is for make_time(hour int, min int, sec float8).
+1
Pavel
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello
So my vote is for make_time(hour int, min int, sec float8).
so here is a patch
Regards
Pavel
Show quoted text
regards, tom lane
Attachments:
make_date.patchapplication/octet-stream; name=make_date.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 6653,6658 **** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
--- 6653,6690 ----
<row>
<entry>
<indexterm>
+ <primary>make_date</primary>
+ </indexterm>
+ <literal><function>make_date(<parameter>year</parameter> <type>int</type>, <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>)</function></literal>
+ </entry>
+ <entry><type>date</type></entry>
+ <entry>
+ Create date from year, month and day fields
+ </entry>
+ <entry><literal>make_date(2013, 7, 15)</literal></entry>
+ <entry><literal>2013-07-15</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_time</primary>
+ </indexterm>
+ <literal><function>make_time(<parameter>hour</parameter> <type>int</type>, <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)</function></literal>
+ </entry>
+ <entry><type>time</type></entry>
+ <entry>
+ Create time from hour, minutes and second fields
+ </entry>
+ <entry><literal>make_time(8, 15, 23.5)</literal></entry>
+ <entry><literal>08:15:23.5</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>now</primary>
</indexterm>
<literal><function>now()</function></literal>
*** a/src/backend/utils/adt/date.c
--- b/src/backend/utils/adt/date.c
***************
*** 2729,2731 **** timetz_izone(PG_FUNCTION_ARGS)
--- 2729,2776 ----
PG_RETURN_TIMETZADT_P(result);
}
+
+ /*
+ * make_date()
+ * date constructor
+ */
+ Datum
+ make_date(PG_FUNCTION_ARGS)
+ {
+ int tm_year = PG_GETARG_INT32(0);
+ int tm_mon = PG_GETARG_INT32(1);
+ int tm_mday = PG_GETARG_INT32(2);
+ DateADT date;
+
+ if (!IS_VALID_JULIAN(tm_year, tm_mon, tm_mday))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range: \"%d-%d-%d\"", tm_year, tm_mon, tm_mday)));
+
+ date = date2j(tm_year, tm_mon, 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;
+
+ #ifdef HAVE_INT64_TIMESTAMP
+ time = (((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec)
+ * USECS_PER_SEC;
+ #else
+ time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec
+ #endif
+
+ PG_RETURN_TIMEADT(time);
+ }
+
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4646,4651 **** DESCR("int8range constructor");
--- 4646,4657 ----
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)");
*** a/src/test/regress/expected/date.out
--- b/src/test/regress/expected/date.out
***************
*** 1184,1186 **** select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
--- 1184,1199 ----
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)
+
*** a/src/test/regress/sql/date.sql
--- b/src/test/regress/sql/date.sql
***************
*** 276,278 **** select 'infinity'::date, '-infinity'::date;
--- 276,283 ----
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);
Hello
updated patch
+ more precious validity check
Regards
Pavel
2013/7/3 Pavel Stehule <pavel.stehule@gmail.com>:
Show quoted text
Hello
So my vote is for make_time(hour int, min int, sec float8).
so here is a patch
Regards
Pavel
regards, tom lane
Attachments:
make_date-v2.patchapplication/octet-stream; name=make_date-v2.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 6653,6658 **** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
--- 6653,6690 ----
<row>
<entry>
<indexterm>
+ <primary>make_date</primary>
+ </indexterm>
+ <literal><function>make_date(<parameter>year</parameter> <type>int</type>, <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>)</function></literal>
+ </entry>
+ <entry><type>date</type></entry>
+ <entry>
+ Create date from year, month and day fields
+ </entry>
+ <entry><literal>make_date(2013, 7, 15)</literal></entry>
+ <entry><literal>2013-07-15</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_time</primary>
+ </indexterm>
+ <literal><function>make_time(<parameter>hour</parameter> <type>int</type>, <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)</function></literal>
+ </entry>
+ <entry><type>time</type></entry>
+ <entry>
+ Create time from hour, minutes and second fields
+ </entry>
+ <entry><literal>make_time(8, 15, 23.5)</literal></entry>
+ <entry><literal>08:15:23.5</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>now</primary>
</indexterm>
<literal><function>now()</function></literal>
*** a/src/backend/utils/adt/date.c
--- b/src/backend/utils/adt/date.c
***************
*** 2729,2731 **** timetz_izone(PG_FUNCTION_ARGS)
--- 2729,2800 ----
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 tange: \"%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 tange: \"%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) + sec)
+ * USECS_PER_SEC;
+ #else
+ time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec
+ #endif
+
+ PG_RETURN_TIMEADT(time);
+ }
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 44,51 **** 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);
--- 44,49 ----
***************
*** 2266,2272 **** 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
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
struct pg_tm * tm)
{
--- 2264,2270 ----
* Check valid year/month/day values, handle BC and DOY cases
* Return 0 if okay, a DTERR code if not.
*/
! int
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
struct pg_tm * tm)
{
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4648,4653 **** DESCR("int8range constructor");
--- 4648,4659 ----
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)");
*** a/src/include/utils/date.h
--- b/src/include/utils/date.h
***************
*** 204,207 **** extern Datum timetz_izone(PG_FUNCTION_ARGS);
--- 204,210 ----
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 */
*** a/src/include/utils/datetime.h
--- b/src/include/utils/datetime.h
***************
*** 310,313 **** extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl);
--- 310,316 ----
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 */
*** a/src/test/regress/expected/date.out
--- b/src/test/regress/expected/date.out
***************
*** 1184,1186 **** select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
--- 1184,1208 ----
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 tange: "2013-2-30"
+ select make_date(2013, 13, 1);
+ ERROR: date field value out of tange: "2013-13-1"
+ select make_time(10, 55, 100.1);
+ ERROR: time field value out of tange: "10:55:100.100000"
+ select make_time(24, 0, 2.1);
+ ERROR: time field value out of tange: "24:00:02.100000"
*** a/src/test/regress/sql/date.sql
--- b/src/test/regress/sql/date.sql
***************
*** 276,278 **** select 'infinity'::date, '-infinity'::date;
--- 276,289 ----
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);
There is a small inconsistency:
select time '12:30:57.123456789';
gives
12:30:57.123457
but
select make_time(12, 30, 57.123456789);
gives
12:30:57.123456
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello
2013/7/12 Peter Eisentraut <peter_e@gmx.net>:
There is a small inconsistency:
select time '12:30:57.123456789';
gives
12:30:57.123457
but
select make_time(12, 30, 57.123456789);
gives
12:30:57.123456
fixed - see attached patch
Regards
Pavel
Show quoted text
Attachments:
make_date-v3.patchapplication/octet-stream; name=make_date-v3.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 6653,6658 **** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
--- 6653,6690 ----
<row>
<entry>
<indexterm>
+ <primary>make_date</primary>
+ </indexterm>
+ <literal><function>make_date(<parameter>year</parameter> <type>int</type>, <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>)</function></literal>
+ </entry>
+ <entry><type>date</type></entry>
+ <entry>
+ Create date from year, month and day fields
+ </entry>
+ <entry><literal>make_date(2013, 7, 15)</literal></entry>
+ <entry><literal>2013-07-15</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_time</primary>
+ </indexterm>
+ <literal><function>make_time(<parameter>hour</parameter> <type>int</type>, <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)</function></literal>
+ </entry>
+ <entry><type>time</type></entry>
+ <entry>
+ Create time from hour, minutes and second fields
+ </entry>
+ <entry><literal>make_time(8, 15, 23.5)</literal></entry>
+ <entry><literal>08:15:23.5</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>now</primary>
</indexterm>
<literal><function>now()</function></literal>
*** a/src/backend/utils/adt/date.c
--- b/src/backend/utils/adt/date.c
***************
*** 2729,2731 **** timetz_izone(PG_FUNCTION_ARGS)
--- 2729,2800 ----
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 tange: \"%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 tange: \"%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);
+ }
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 44,51 **** 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);
--- 44,49 ----
***************
*** 2266,2272 **** 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
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
struct pg_tm * tm)
{
--- 2264,2270 ----
* Check valid year/month/day values, handle BC and DOY cases
* Return 0 if okay, a DTERR code if not.
*/
! int
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
struct pg_tm * tm)
{
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4648,4653 **** DESCR("int8range constructor");
--- 4648,4659 ----
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)");
*** a/src/include/utils/date.h
--- b/src/include/utils/date.h
***************
*** 204,207 **** extern Datum timetz_izone(PG_FUNCTION_ARGS);
--- 204,210 ----
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 */
*** a/src/include/utils/datetime.h
--- b/src/include/utils/datetime.h
***************
*** 310,313 **** extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl);
--- 310,316 ----
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 */
*** a/src/test/regress/expected/date.out
--- b/src/test/regress/expected/date.out
***************
*** 1184,1186 **** select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
--- 1184,1208 ----
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 tange: "2013-2-30"
+ select make_date(2013, 13, 1);
+ ERROR: date field value out of tange: "2013-13-1"
+ select make_time(10, 55, 100.1);
+ ERROR: time field value out of tange: "10:55:100.100000"
+ select make_time(24, 0, 2.1);
+ ERROR: time field value out of tange: "24:00:02.100000"
*** a/src/test/regress/sql/date.sql
--- b/src/test/regress/sql/date.sql
***************
*** 276,278 **** select 'infinity'::date, '-infinity'::date;
--- 276,289 ----
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);
Pavel Stehule escribi�:
fixed - see attached patch
There's a typo "tange" in some error messages, which has found its way
to the regression tests.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hi Pavel,
I have reviewed your patch.
Patch looks excellent and code changes match with similar constructs
elsewhere. That's great.
However, it was not applying with git apply command but able to apply it
with patch -p1 with some offsets. make and make install was smooth too.
Regression suite didn't complain as expected.
I did my own testing and din't get any issues with that. Code walk-through
was good too.
I was little bit worried as we are allowing 60 for seconds in which case we
are wrapping it to next minute and setting sec to 0. But this logic was not
true for minutes. There we are throwing an error when min = 60.
But I don't blame on this patch as other constructs does same too. Like
"select time '15:60:20'" throws an error where as "select time '15:30:60'"
does not.
However, in attached patch I have fixed the typo identified by Alvaro.
Please have a look before I submit it to the committer.
Thanks
On Sat, Jul 13, 2013 at 5:32 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
Hello
2013/7/12 Peter Eisentraut <peter_e@gmx.net>:
There is a small inconsistency:
select time '12:30:57.123456789';
gives
12:30:57.123457
but
select make_time(12, 30, 57.123456789);
gives
12:30:57.123456
fixed - see attached patch
Regards
Pavel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20 30589500
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.
Attachments:
make_date_v4.patchapplication/octet-stream; name=make_date_v4.patchDownload
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})');
<row>
<entry>
<indexterm>
+ <primary>make_date</primary>
+ </indexterm>
+ <literal><function>make_date(<parameter>year</parameter> <type>int</type>, <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>)</function></literal>
+ </entry>
+ <entry><type>date</type></entry>
+ <entry>
+ Create date from year, month and day fields
+ </entry>
+ <entry><literal>make_date(2013, 7, 15)</literal></entry>
+ <entry><literal>2013-07-15</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_time</primary>
+ </indexterm>
+ <literal><function>make_time(<parameter>hour</parameter> <type>int</type>, <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)</function></literal>
+ </entry>
+ <entry><type>time</type></entry>
+ <entry>
+ Create time from hour, minutes and second fields
+ </entry>
+ <entry><literal>make_time(8, 15, 23.5)</literal></entry>
+ <entry><literal>08:15:23.5</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 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);
Hello
thank you,
I have no comments
Regards
Pavel
2013/9/18 Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Show quoted text
Hi Pavel,
I have reviewed your patch.
Patch looks excellent and code changes match with similar constructs
elsewhere. That's great.However, it was not applying with git apply command but able to apply it
with patch -p1 with some offsets. make and make install was smooth too.
Regression suite didn't complain as expected.I did my own testing and din't get any issues with that. Code walk-through
was good too.I was little bit worried as we are allowing 60 for seconds in which case we
are wrapping it to next minute and setting sec to 0. But this logic was not
true for minutes. There we are throwing an error when min = 60.But I don't blame on this patch as other constructs does same too. Like
"select time '15:60:20'" throws an error where as "select time '15:30:60'"
does not.However, in attached patch I have fixed the typo identified by Alvaro.
Please have a look before I submit it to the committer.
Thanks
On Sat, Jul 13, 2013 at 5:32 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
Hello
2013/7/12 Peter Eisentraut <peter_e@gmx.net>:
There is a small inconsistency:
select time '12:30:57.123456789';
gives
12:30:57.123457
but
select make_time(12, 30, 57.123456789);
gives
12:30:57.123456
fixed - see attached patch
Regards
Pavel
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL CompanyPhone: +91 20 30589500
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedbThis e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.
On Wed, Sep 18, 2013 at 9:54 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
Hello
thank you,
I have no comments
Thanks.
Assigned it to committer.
Regards
Pavel
--
Jeevan B Chalke
Jeevan Chalke escribi�:
On Wed, Sep 18, 2013 at 9:54 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
thank you,
I have no comments
Assigned it to committer.
Hm, these functions are marked as STABLE, right? Why aren't they
immutable?
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Hello
2013/10/11 Alvaro Herrera <alvherre@2ndquadrant.com>
Jeevan Chalke escribió:
On Wed, Sep 18, 2013 at 9:54 PM, Pavel Stehule <pavel.stehule@gmail.com
wrote:thank you,
I have no comments
Assigned it to committer.
Hm, these functions are marked as STABLE, right? Why aren't they
immutable?
It was my mistake - I was confused from timestamp with time zone type,
what has zero related to date and time.
fixed to immutable,
fixed duplicate oid
Regards
Pavel
Show quoted text
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
make_date_v5.patchapplication/octet-stream; name=make_date_v5.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e397386..79f4b56 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})');
<row>
<entry>
<indexterm>
+ <primary>make_date</primary>
+ </indexterm>
+ <literal><function>make_date(<parameter>year</parameter> <type>int</type>, <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>)</function></literal>
+ </entry>
+ <entry><type>date</type></entry>
+ <entry>
+ Create date from year, month and day fields
+ </entry>
+ <entry><literal>make_date(2013, 7, 15)</literal></entry>
+ <entry><literal>2013-07-15</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_time</primary>
+ </indexterm>
+ <literal><function>make_time(<parameter>hour</parameter> <type>int</type>, <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)</function></literal>
+ </entry>
+ <entry><type>time</type></entry>
+ <entry>
+ Create time from hour, minutes and second fields
+ </entry>
+ <entry><literal>make_time(8, 15, 23.5)</literal></entry>
+ <entry><literal>08:15:23.5</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 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 f39353f..85a76c1 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 08586ae..c6804c6 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4662,6 +4662,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 = 3969 ( 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 = 3970 ( 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");
+
/* 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);
Pavel Stehule escribi�:
It was my mistake - I was confused from timestamp with time zone type,
what has zero related to date and time.fixed to immutable,
fixed duplicate oid
Thanks. I wasn't sure about the error message returned when times are
outside range; how about this instead? I'm not wedded to this approach
-- I can return to yours if this one isn't liked -- but I think the
more specific messages are better. I realize this is inconsistent with
the make_date case which always displays the full date instead of
specific fields, but I think it's more likely that someone is doing
arithmetic to enter time fields than date. (Anyway maybe this is not an
important enough issue to create more work for translators.)
+ if (tm_hour < 0 || tm_hour > HOURS_PER_DAY)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("hours field in time value out of range: \"%02d\"",
+ tm_hour)));
+
+ if (tm_min < 0 || tm_min > MINS_PER_HOUR - 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("minutes field in time value out of range: \"%02d\"",
+ tm_min)));
+
+ if (sec < 0.0 || sec > (float8) SECS_PER_MINUTE)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("seconds field in time value out of range: \"%0*.*f\"",
+ MAX_TIME_PRECISION + 3,
+ MAX_TIME_PRECISION, fabs(sec))));
+
+ /* test for > 24:00:00 */
+ if ((tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0.0)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("time value out of range: \"%02d:%02d:%0*.*f\"",
+ tm_hour, tm_min,
+ MAX_TIME_PRECISION + 3,
+ MAX_TIME_PRECISION, fabs(sec))));Other than that (and fixing regression tests as appropriate), I think
the attached, which has mild corrections over your v5, is ready to
commit. (You had one missing semicolon in the float timestamp case.)
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
make_date_v6.patchtext/x-diff; charset=us-asciiDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 6669,6674 **** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
--- 6669,6716 ----
<row>
<entry>
<indexterm>
+ <primary>make_date</primary>
+ </indexterm>
+ <literal>
+ <function>
+ make_date(<parameter>year</parameter> <type>int</type>,
+ <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>)
+ </function>
+ </literal>
+ </entry>
+ <entry><type>date</type></entry>
+ <entry>
+ Create date from year, month and day fields
+ </entry>
+ <entry><literal>make_date(2013, 7, 15)</literal></entry>
+ <entry><literal>2013-07-15</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_time</primary>
+ </indexterm>
+ <literal>
+ <function>
+ make_time(<parameter>hour</parameter> <type>int</type>,
+ <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)
+ </function>
+ </literal>
+ </entry>
+ <entry><type>time</type></entry>
+ <entry>
+ Create time from hour, minutes and second fields
+ </entry>
+ <entry><literal>make_time(8, 15, 23.5)</literal></entry>
+ <entry><literal>08:15:23.5</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>now</primary>
</indexterm>
<literal><function>now()</function></literal>
*** a/src/backend/utils/adt/date.c
--- b/src/backend/utils/adt/date.c
***************
*** 2729,2731 **** timetz_izone(PG_FUNCTION_ARGS)
--- 2729,2815 ----
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_DATE_M, 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_hour > HOURS_PER_DAY)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("hours field in time value out of range: \"%02d\"",
+ tm_hour)));
+
+ if (tm_min < 0 || tm_min > MINS_PER_HOUR - 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("minutes field in time value out of range: \"%02d\"",
+ tm_min)));
+
+ if (sec < 0.0 || sec > (float8) SECS_PER_MINUTE)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("seconds field in time value out of range: \"%0*.*f\"",
+ MAX_TIME_PRECISION + 3,
+ MAX_TIME_PRECISION, fabs(sec))));
+
+ /* test for > 24:00:00 */
+ if ((tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0.0)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("time 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);
+ }
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 44,51 **** 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);
--- 44,49 ----
***************
*** 2266,2272 **** 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
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
struct pg_tm * tm)
{
--- 2264,2270 ----
* Check valid year/month/day values, handle BC and DOY cases
* Return 0 if okay, a DTERR code if not.
*/
! int
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
struct pg_tm * tm)
{
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4662,4667 **** DESCR("int8range constructor");
--- 4662,4673 ----
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 = 3969 ( 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 = 3970 ( 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");
+
/* 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)");
*** a/src/include/utils/date.h
--- b/src/include/utils/date.h
***************
*** 204,207 **** extern Datum timetz_izone(PG_FUNCTION_ARGS);
--- 204,210 ----
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 */
*** a/src/include/utils/datetime.h
--- b/src/include/utils/datetime.h
***************
*** 310,313 **** extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl);
--- 310,316 ----
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 */
*** a/src/test/regress/expected/date.out
--- b/src/test/regress/expected/date.out
***************
*** 1184,1186 **** select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
--- 1184,1208 ----
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"
*** a/src/test/regress/sql/date.sql
--- b/src/test/regress/sql/date.sql
***************
*** 276,278 **** select 'infinity'::date, '-infinity'::date;
--- 276,289 ----
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);
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
Thanks. I wasn't sure about the error message returned when times are
outside range; how about this instead? I'm not wedded to this approach
-- I can return to yours if this one isn't liked -- but I think the
more specific messages are better. I realize this is inconsistent with
the make_date case which always displays the full date instead of
specific fields, but I think it's more likely that someone is doing
arithmetic to enter time fields than date. (Anyway maybe this is not an
important enough issue to create more work for translators.)
I thought that last point was the most important one: doing it like that
would create more work for translators than it's worth. There's no reason
to think that people can't figure out which field it's unhappy about.
And what if more than one field is wrong? You'd be exposing an
implementation detail about the order in which the tests are made.
Another issue with the patch as submitted was that make_date with a
negative year value behaved unreasonably. I made it throw error, but
you could also argue that say "-44" ought to mean "44 BC". (Year zero
should be disallowed in any case, of course.) It would take a few
extra lines of code to do that.
Committed with those changes and some other cosmetic adjustments.
This doesn't really finish the TODO item, as that contemplated a
make_timestamp() function as well; but I don't see a reason not
to commit what we've got.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers