QSoC proposal: date_trunc supporting intervals
Hello!
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.
*
PostgreSQL GSoC 2014 proposal
Project name
date_trunc() supporting intervals
Short description
The function date_trunc () is conceptually similar to the trunc function
for numbers. But now it doesn’t have full functionality because
intervals are not supporting intervals in date_trunc ().
Name: Alexander Shvidchenko
E-mail: askellio@gmail.com <mailto:askellio@gmail.com>
Location: Rostov-on-Don, Russia (UTC +04.00)
Benefits to the PostgreSQL Community
This feature will expand opportunities to work with time in databases.
It will do the job with time more flexible and easier.
Quantifiable results
Supporting and correct working with intervals by date_trunc ()
Project Schedule
until May 31
Make code review and solve architecture questions with help of community
1 June – 30 June
Detailed implementation of libraries.
1 July – 31 July
Finish Implementation of libraires and begin testing.
1 August -15 August
Final refactoring, testing and commit.
Some details
In the period until May 31 I need to discover what types of intervals
are able to be sent. Also I need to set the stamp of the result.
For example:
date_trunc (‘week’, ‘1 month 15 day’:interval)
result
‘1 month 14 day’
or
‘1 month 2 week’
It seems like this project idea isn’t very difficult and large. So if I
have time after finishing this job I’ll be able to work more: close some
bugs or realize some more features that will be usefull for the community.
Academic experience
I entered the university in 2013. Before entering the university I
finished the college in 2012. My graduate work in the college was the
client-server application. It was a realization of XMPP. The client was
realized in Qt. The client worked with SQLite database and the server
worked with MySQL database.
Why is PostgreSQL?
- I’m intereted in this idea and believe this project would be useful
for the community;
- PostgreSQL is a very respected community. I would be proud to be a
part of it;
- PostgreSQL is one of the best DBMS and I would like to make it better.
Links
1) PostgreSQL 9.3.3 Documentation, date_trunc
http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
*
With best wishes,
Alexander S.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM1c0e1daffbc1dd24540da29fa2a6d35e6a90f9da8917e25ab495713b2f7e65e61d6beb696268b93a0b39c5c133df7e05@asav-1.01.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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?
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMd5a8ab8c2966ee528a30df7b60d936bddeefd906d25cd7a895b923463571898a04ef6b26de1512a464590aa491c5610f@asav-1.01.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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?
With best wishes,
Alexander S.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM3f684155313c7f39de99b88d3dd424c1eed60a0e45b38573097f68687aae7209bb7d7251938cf75588341a9adacdf1d0@asav-1.01.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers