Обсуждение: QSoC proposal: date_trunc supporting intervals
Hello!<br /> Here is the text of my proposal which I've applied to GSoC.<br /> (and link <a class="moz-txt-link-freetext" href="https://docs.google.com/document/d/1vBjQzhFT_fgoIkoEP5TVeyFA6ggsYlLq76tghGVUD6A/edit?usp=sharing">https://docs.google.com/document/d/1vBjQzhFT_fgoIkoEP5TVeyFA6ggsYlLq76tghGVUD6A/edit?usp=sharing</a>)<br />Any suggestions and comments are welcome.<br /> Because I don't know the code of PostgreSQL well I decide not to participateis QSoC with previous proposal (rewrite pg_dump and pg_restore as libraries). But I'm very interested to participatein QSoC 2014 as a part of PostgreSQL. So It's my new proposal.<br /><br /><b id="docs-internal-guid-0cf99db7-e067-6766-231f-f53c0f998634"style="font-weight:normal;"><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: center;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:transparent;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">PostgreSQL GSoC2014 proposal</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:transparent;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Project name</span><pdir="ltr" style="line-height:1.15;margin-top:3pt;margin-bottom:1pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">date_trunc() supportingintervals</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Short description</span><pdir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">The functiondate_trunc () is conceptually similar to the trunc function for numbers. But now it doesn’t have full functionalitybecause intervals are not supporting intervals in date_trunc ().</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Name</span><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">: AlexanderShvidchenko</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">E-mail</span><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">: </span><ahref="mailto:askellio@gmail.com" style="text-decoration:none;"><span style="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap;">askellio@gmail.com</span></a><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;"></span><p dir="ltr"style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Location</span><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">: Rostov-on-Don,Russia (UTC +04.00)</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Benefits tothe PostgreSQL Community</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">This featurewill expand opportunities to work with time in databases. It will do the job with time more flexible and easier.</span><pdir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Quantifiable results</span><pdir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Supporting andcorrect working with intervals by date_trunc ()</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Project Schedule</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">until May31 </span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Make codereview and solve architecture questions with help of community</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">1 June– 30 June</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Detailed implementationof libraries.</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">1 July– 31 July</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Finish Implementationof libraires and begin testing. </span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">1 August-15 August</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Final refactoring,testing and commit.</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Some details</span><pdir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">In theperiod until May 31 I need to discover what types of intervals are able to be sent. Also I need to set the stamp of theresult.</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">For example:</span><pdir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-indent: 36pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">date_trunc (‘week’,‘1 month 15 day’:interval)</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">result</span><p dir="ltr"style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-indent: 36pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">‘1 month14 day’</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">or</span><p dir="ltr"style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;"><span class="Apple-tab-span"style="white-space:pre;"> </span>‘1 month 2 week’</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">It seemslike this project idea isn’t very difficult and large. So if I have time after finishing this job I’ll be able to workmore: close some bugs or realize some more features that will be usefull for the community.</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;"> Academicexperience</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">I enteredthe university in 2013. Before entering the university I finished the college in 2012. My graduate work in the collegewas the client-server application. It was a realization of XMPP. The client was realized in Qt. The client workedwith SQLite database and the server worked with MySQL database.</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Why isPostgreSQL?</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">- I’mintereted in this idea and believe this project would be useful for the community;</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">- PostgreSQLis a very respected community. I would be proud to be a part of it;</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">- PostgreSQLis one of the best DBMS and I would like to make it better.</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:bold;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">Links</span><p dir="ltr"style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">1) PostgreSQL9.3.3 Documentation, date_trunc</span><p dir="ltr" style="line-height:1.15;margin-top:0pt;margin-bottom:0pt;text-align: justify;"><a href="http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC" style="text-decoration:none;"><spanstyle="font-size:16px;font-family:'Times New Roman';color:#1155cc;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:underline;vertical-align:baseline;white-space:pre-wrap;">http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC</span></a><span style="font-size:16px;font-family:'TimesNew Roman';color:#000000;background-color:#ffffff;font-weight:normal;font-style:normal;font-variant:normal;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;"></span></b><br class="Apple-interchange-newline"/> With best wishes,<br /> Alexander S.<br />
On 03/20/2014 09:56 AM, Alexandr wrote: > Here is the text of my proposal which I've applied to GSoC. > (and link > https://docs.google.com/document/d/1vBjQzhFT_fgoIkoEP5TVeyFA6ggsYlLq76tghGVUD6A/edit?usp=sharing) > > Any suggestions and comments are welcome. > Because I don't know the code of PostgreSQL well I decide not to > participate is QSoC with previous proposal (rewrite pg_dump and > pg_restore as libraries). But I'm very interested to participate in QSoC > 2014 as a part of PostgreSQL. So It's my new proposal. Per my comments on the GSOC app, it looks good, but I'd like to see some "stretch goals" if you are able to implement the new function before GSOC is over. For example, one thing which has been frequently requested is functions to display intervals in the unit of your choice ... for example, convert "1 day" to "14400 seconds". Pick some stretch goals which work for you ... but I'd like to see some. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 20 March 2014 20:07, Josh Berkus <josh@agliodbs.com> wrote: > On 03/20/2014 09:56 AM, Alexandr wrote: >> Here is the text of my proposal which I've applied to GSoC. >> (and link >> https://docs.google.com/document/d/1vBjQzhFT_fgoIkoEP5TVeyFA6ggsYlLq76tghGVUD6A/edit?usp=sharing) >> >> Any suggestions and comments are welcome. >> Because I don't know the code of PostgreSQL well I decide not to >> participate is QSoC with previous proposal (rewrite pg_dump and >> pg_restore as libraries). But I'm very interested to participate in QSoC >> 2014 as a part of PostgreSQL. So It's my new proposal. > > Per my comments on the GSOC app, it looks good, but I'd like to see some > "stretch goals" if you are able to implement the new function before > GSOC is over. For example, one thing which has been frequently > requested is functions to display intervals in the unit of your choice > ... for example, convert "1 day" to "14400 seconds". +1 This is definitely something I've wanted in the past, like getting the number of minutes between 2 timestamps without converting to seconds since epoch then doing a subtraction. like: date_diff(timestamptz, timestamptz, interval) returns decimal # SELECT date_diff('2014-02-04 12:44:18+0'::timestamptz, '2014-02-08 20:10:05+0'::timestamptz, '1 second');date_diff ----------- 372347 (1 row) # SELECT date_diff('2014-02-04 12:44:18+0'::timestamptz, '2014-02-08 20:10:05+0'::timestamptz, '5 seconds');date_diff ----------- 74469 (1 row) # SELECT date_diff('2014-02-04 12:44:18+0'::timestamptz, '2014-02-08 20:10:05+0'::timestamptz, '1 day'); date_diff --------------------4.3095717592592593 (1 row) Although perhaps there's a more flexible and useful way of doing this that. One would probably want to convert an interval to such units too, like '3 days' in seconds. -- Thom
<br /><div class="moz-cite-prefix">21.03.2014 00:07, Josh Berkus пишет:<br /></div><blockquote cite="mid:532B4A69.6060103@agliodbs.com"type="cite"> Per my comments on the GSOC app, it looks good, but I'd like to seesome "stretch goals" if you are able to implement the new function before GSOC is over. For example, one thing which hasbeen frequently requested is functions to display intervals in the unit of your choice ... for example, convert "1 day"to "14400 seconds". Pick some stretch goals which work for you ... but I'd like to see some. </blockquote> I looked throughTODO and found only 2 ideas with intervals:<br /><span style="color: rgb(0, 0, 0); font-family: sans-serif; font-size:16px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 24.28874969482422px; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display:inline !important; float: none;">1) Allow infinite intervals just like infinite timestamps<br /> 2) </span> <spanstyle="color: rgb(0, 0, 0); font-family: sans-serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 24.28874969482422px; orphans: auto; text-align:start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width:0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;">Havetimestamp subtraction not call justify_hours() (formatting intervals with to_chars)<br /> I want to add theseideas as stretch goals:<br /> 1) extract_total() - </span>allows conversation of the interval to a total number of theuser's desired unit<br /><span style="color: rgb(0, 0, 0); font-family: sans-serif; font-size: 16px; font-style: normal;font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 24.28874969482422px; orphans:auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing:0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); display: inline !important; float: none;"> 2) Allow TIMESTAMP WITH TIME ZONE<br /> 3) add function to allow the creation of timestamps using parameters<br/> 4) Add function to detect if an array is empty<br /> Josh, what do you think about them?<br /></span>
On 03/20/2014 01:26 PM, Alexandr wrote: > > 21.03.2014 00:07, Josh Berkus пишет: >> Per my comments on the GSOC app, it looks good, but I'd like to see >> some "stretch goals" if you are able to implement the new function >> before GSOC is over. For example, one thing which has been frequently >> requested is functions to display intervals in the unit of your choice >> ... for example, convert "1 day" to "14400 seconds". Pick some stretch >> goals which work for you ... but I'd like to see some. > I looked through TODO and found only 2 ideas with intervals: > 1) Allow infinite intervals just like infinite timestamps > 2) Have timestamp subtraction not call justify_hours() (formatting > intervals with to_chars) > I want to add these ideas as stretch goals: > 1) extract_total() - allows conversation of the interval to a total > number of the user's desired unit > 2) Allow TIMESTAMP WITH TIME ZONE > 3) add function to allow the creation of timestamps using parameters > 4) Add function to detect if an array is empty > Josh, what do you think about them? Comments: #2: I don't understand this one? #3 is already a patch for version 9.4, but possibly you can improve/expand it. #4 has already been the subject of a LOT of debate, I think you don't want to get into it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Mar 20, 2014, at 1:24 PM, Thom Brown <thom@linux.com> wrote: > On 20 March 2014 20:07, Josh Berkus <josh@agliodbs.com> wrote: >> On 03/20/2014 09:56 AM, Alexandr wrote: >>> Here is the text of my proposal which I've applied to GSoC. >>> (and link >>> https://docs.google.com/document/d/1vBjQzhFT_fgoIkoEP5TVeyFA6ggsYlLq76tghGVUD6A/edit?usp=sharing) >>> >>> Any suggestions and comments are welcome. >>> Because I don't know the code of PostgreSQL well I decide not to >>> participate is QSoC with previous proposal (rewrite pg_dump and >>> pg_restore as libraries). But I'm very interested to participate in QSoC >>> 2014 as a part of PostgreSQL. So It's my new proposal. >> >> Per my comments on the GSOC app, it looks good, but I'd like to see some >> "stretch goals" if you are able to implement the new function before >> GSOC is over. For example, one thing which has been frequently >> requested is functions to display intervals in the unit of your choice >> ... for example, convert "1 day" to "14400 seconds". > > +1 > > This is definitely something I've wanted in the past, like getting the > number of minutes between 2 timestamps without converting to seconds > since epoch then doing a subtraction. It’d be nice, but isn’t it impossible with anything similar to the existing interval type (as you lose data when you convert to an interval that you can’t get back)? Subtracting to get an interval, then converting that interval to seconds or minutes could give you a value that’s wildly different from the right answer. Cheers, Steve
<br /><div class="moz-cite-prefix">21.03.2014 00:33, Josh Berkus пишет:<br /></div><blockquote cite="mid:532B50AB.9070505@agliodbs.com"type="cite"> Comments:<br /> #2: I don't understand this one?<br /> #3 is alreadya patch for version 9.4, but possibly you can improve/expand it.<br /> #4 has already been the subject of a LOT ofdebate, I think you don't want to get into it. </blockquote><span style="color: rgb(0, 0, 0); font-family: sans-serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal;line-height: 24.28874969482422px; orphans: auto; text-align: start; text-indent: 0px; text-transform: none;white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255,255, 255); display: inline !important; float: none;">I meaned this one: Allow TIMESTAMP WITH TIME ZONE to storethe original timezone information, either zone name or offset from UTC<br /> And which ideas can you advise me to addto proposal?<br /><br /></span>With best wishes,<br /> Alexander S.<br />
> I meaned this one: Allow TIMESTAMP WITH TIME ZONE to store the original > timezone information, either zone name or offset from UTC > And which ideas can you advise me to add to proposal? That one has also been hotly debated. You'd probably have to do it as an extension, and that would be a fairly large stretch goal. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Alexandr escribió: > > 21.03.2014 00:33, Josh Berkus пишет: > >Comments: > >#2: I don't understand this one? > >#3 is already a patch for version 9.4, but possibly you can > >improve/expand it. > >#4 has already been the subject of a LOT of debate, I think you > >don't want to get into it. > I meaned this one: Allow TIMESTAMP WITH TIME ZONE to store the > original timezone information, either zone name or offset from UTC > And which ideas can you advise me to add to proposal? This has been discussed previously. I doubt it makes a good GSoC project. Maybe if you were to create a new datatype that stored the timestamptz plus the original timezone separately, it'd work better; however I vaguely remember we discussed this a long time ago. One of the challenges was how to store the timezone; we didn't want to spend as much as the whole text representation, so we wanted a catalog that attached an OID to each timezone. It got real messy from there, and we dropped the idea. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
> Subtracting to get an interval, then converting that interval to > seconds or minutes could give you a value that’s wildly different from > the right answer. Can you explain me when it happens ? With best wishes, Alexander S.
On Thu, Mar 20, 2014 at 5:55 PM, Alexandr <askellio@gmail.com> wrote: >> Subtracting to get an interval, then converting that interval to seconds >> or minutes could give you a value that's wildly different from the right >> answer. > > Can you explain me when it happens ? '1 month'::interval It's different depending on which month we're talking about.