Re: [HACKERS] proposal: simple date constructor from numeric values
Alvaro Herrera 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
Re: [HACKERS] proposal: simple date constructor from numeric values
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** *** 6669,6674 SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); --- 6669,6716 + make_date + + + + make_date(year int, + month int, + day int) + + + + date + + Create date from year, month and day fields + + make_date(2013, 7, 15) + 2013-07-15 + + + + + + make_time + + + +make_time(hour int, +min int, +sec double precision) + + + + time + + Create time from hour, minutes and second fields + + make_time(8, 15, 23.5) + 08:15:23.5 + + + + + now now() *** 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 |
Re: [HACKERS] proposal: simple date constructor from numeric values
Hello 2013/10/11 Alvaro Herrera > Jeevan Chalke escribió: > > On Wed, Sep 18, 2013 at 9:54 PM, Pavel Stehule >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 > > -- > Álvaro Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > make_date_v5.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: simple date constructor from numeric values
Jeevan Chalke escribió: > On Wed, Sep 18, 2013 at 9:54 PM, Pavel Stehule 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 Herrerahttp://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
Re: [HACKERS] proposal: simple date constructor from numeric values
On Wed, Sep 18, 2013 at 9:54 PM, Pavel Stehule wrote: > Hello > > thank you, > > I have no comments > Thanks. Assigned it to committer. > > Regards > > Pavel > > -- Jeevan B Chalke
Re: [HACKERS] proposal: simple date constructor from numeric values
Hello thank you, I have no comments Regards Pavel 2013/9/18 Jeevan Chalke > 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 wrote: > >> Hello >> >> 2013/7/12 Peter Eisentraut : >> > 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. >
Re: [HACKERS] proposal: simple date constructor from numeric values
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 wrote: > Hello > > 2013/7/12 Peter Eisentraut : > > 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. make_date_v4.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: simple date constructor from numeric values
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 Herrerahttp://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
Re: [HACKERS] proposal: simple date constructor from numeric values
Hello 2013/7/12 Peter Eisentraut : > 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 > make_date-v3.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: simple date constructor from numeric values
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
Re: [HACKERS] proposal: simple date constructor from numeric values
Hello updated patch + more precious validity check Regards Pavel 2013/7/3 Pavel Stehule : > Hello > > >> So my vote is for make_time(hour int, min int, sec float8). >> > > so here is a patch > > Regards > > Pavel > > > >> regards, tom lane make_date-v2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: simple date constructor from numeric values
Hello > So my vote is for make_time(hour int, min int, sec float8). > so here is a patch Regards Pavel > regards, tom lane make_date.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: simple date constructor from numeric values
2013/7/3 Tom Lane : > Alvaro Herrera 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
Re: [HACKERS] proposal: simple date constructor from numeric values
Alvaro Herrera 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
Re: [HACKERS] proposal: simple date constructor from numeric values
2013/7/3 Alvaro Herrera : > 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 Herrerahttp://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
Re: [HACKERS] proposal: simple date constructor from numeric values
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 Herrerahttp://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
Re: [HACKERS] proposal: simple date constructor from numeric values
On 3 July 2013 21:41, Pavel Stehule 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
Re: [HACKERS] proposal: simple date constructor from numeric values
Hello 2013/7/3 Brendan Jurd : > On 1 July 2013 17:47, Pavel Stehule wrote: >> 2013/6/29 Pavel Stehule : >>> 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 >> >> http://www.postgresql.org/message-id/14107.1276443...@sss.pgh.pa.us >> > > I suggested something similar also: > > http://www.postgresql.org/message-id/AANLkTi=W1wtcL7qR4PuQaQ=uoabmjsusz6qgjtucx...@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
Re: [HACKERS] proposal: simple date constructor from numeric values
On 1 July 2013 17:47, Pavel Stehule wrote: > 2013/6/29 Pavel Stehule : >> 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 > > http://www.postgresql.org/message-id/14107.1276443...@sss.pgh.pa.us > I suggested something similar also: http://www.postgresql.org/message-id/AANLkTi=W1wtcL7qR4PuQaQ=uoabmjsusz6qgjtucx...@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
Re: [HACKERS] proposal: simple date constructor from numeric values
2013/7/3 Pavel Stehule : > 2013/7/2 Pavel Stehule : >> 2013/7/1 Peter Eisentraut : >>> 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
Re: [HACKERS] proposal: simple date constructor from numeric values
2013/7/2 Pavel Stehule : > 2013/7/1 Peter Eisentraut : >> 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
Re: [HACKERS] proposal: simple date constructor from numeric values
2013/7/1 Peter Eisentraut : > 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
Re: [HACKERS] proposal: simple date constructor from numeric values
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
Re: [HACKERS] proposal: simple date constructor from numeric values
Hello 2013/6/29 Pavel Stehule : > 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 http://www.postgresql.org/message-id/14107.1276443...@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