Thread: [RFC] allow 'semester' in functions EXTRACT, DATE_PART, TO_CHAR and so on.

Hello hackers,

* Description

This patch is a proposal to allow the use of word 'semester' to
extract it from date in functions like EXTRACT, DATE_PART, etc and
adds the letter 'S' to format the date output in to_char.

** Example

SELECT EXTRACT(semester FROM DATE '2015-07-07');

date_part
-----------        2

* Motivation

The term is used in a school or college to represent a half-year.
Actually it could be evaluated from a date using some math with
'quarter' but could not be extracted from date since the API to define
a "reserved word" for EXTRACT is in the core, rather than in SQL.

* Syntax

I'm using the word 'semester' since it is common in college and school
but I wondering if it should be other word (like 'halfyear', for
example)

Is this a feature that worth?

BTW, I'll put this in the commit fest (2015-09).

Thanks!
-- 
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br

2015-08-14 20:58 GMT-03:00 Dickson S. Guedes <listas@guedesoft.net>:
> Hello hackers,
>
> * Description
>
> This patch is a proposal

Now patch attached,  sorry.

--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br

On 15/08/15 11:58, Dickson S. Guedes wrote:
> Hello hackers,
>
> * Description
>
> This patch is a proposal to allow the use of word 'semester' to
> extract it from date in functions like EXTRACT, DATE_PART, etc and
> adds the letter 'S' to format the date output in to_char.
>
> ** Example
>
> SELECT EXTRACT(semester FROM DATE '2015-07-07');
>
> date_part
> -----------
>           2
>
> * Motivation
>
> The term is used in a school or college to represent a half-year.
> Actually it could be evaluated from a date using some math with
> 'quarter' but could not be extracted from date since the API to define
> a "reserved word" for EXTRACT is in the core, rather than in SQL.
>
> * Syntax
>
> I'm using the word 'semester' since it is common in college and school
> but I wondering if it should be other word (like 'halfyear', for
> example)
>
> Is this a feature that worth?
>
> BTW, I'll put this in the commit fest (2015-09).
>
> Thanks!
Note that the start and end points for a semester might well be 
different between universities in the Northern & Southern Hemispheres of 
this planet- also between institutions in the same hemisphere.

The first semester in 2015 for the University of Auckland begins Monday 
2 March 2015:
https://www.auckland.ac.nz/en/for/current-students/cs-academic-information/cs-academic-dates.html

The first semester in 2015 for ANU (Australian National University) 
begins Monday 16 February 2015:
http://www.anu.edu.au/directories/university-calendar

So semesters don't appear to align with normal half year boundaries.


Cheers,
Gavin

The term is used in a school or college to represent a half-year.
Actually it could be evaluated from a date using some math with
'quarter' but could not be extracted from date since the API to define
a "reserved word" for EXTRACT is in the core, rather than in SQL.

2015-08-14 21:32 GMT-03:00 Gavin Flower <GavinFlower@archidevsys.co.nz>:
...
> So semesters don't appear to align with normal half year boundaries.

Interesting links, thanks!

Which sounds better for a native English: 'half', 'halfyear'?

For example:

> SELECT date_trunc('halfyear', current_date);      date_trunc
------------------------2015-07-01 00:00:00-03
(1 row)

Thanks!
-- 
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br

On 15/08/15 13:06, Dickson S. Guedes wrote:
> 2015-08-14 21:32 GMT-03:00 Gavin Flower <GavinFlower@archidevsys.co.nz>:
> ...
>> So semesters don't appear to align with normal half year boundaries.
> Interesting links, thanks!
>
> Which sounds better for a native English: 'half', 'halfyear'?
>
> For example:
>
>> SELECT date_trunc('halfyear', current_date);
>         date_trunc
> ------------------------
>   2015-07-01 00:00:00-03
> (1 row)
>
> Thanks!
I would go with 'halfyear', but I don't have a use case for it, so don't 
put too much weight on my opinion of this!

2015-08-14 21:40 GMT-03:00 David G. Johnston <david.g.johnston@gmail.com>:
> On Friday, August 14, 2015, Dickson S. Guedes <listas@guedesoft.net> wrote:
>>
>>
>> The term is used in a school or college to represent a half-year.
>> Actually it could be evaluated from a date using some math with
>> 'quarter' but could not be extracted from date since the API to define
>> a "reserved word" for EXTRACT is in the core, rather than in SQL.
>>
>
> If this is wanted a more consistent word would be "half" since we already
> accept quarter.

In your opinion: "half" or "halfyear" ?

> I quite dislike semester because of its connotations in education and the
> fact that actual semesters do not align with calendar halves.

I agree, my native language is pt_BR, and it is very common to use
"semestre" ( "semester" ), but reading some text around the Internet I
see "half-year" more often.

Thank you.
-- 
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br

On Fri, Aug 14, 2015 at 8:40 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> Tbh, "quarter in (1,2)" does not seem that problematic...

Yeah.  It's so easy to compute the half from the quarter (or even from
the month) that I can't see adding this.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, August 14, 2015, Dickson S. Guedes <listas@guedesoft.net> wrote:
>> The term is used in a school or college to represent a half-year.

> I quite dislike semester because of its connotations in education and the
> fact that actual semesters do not align with calendar halves.

It's worse than that: some institutions use "semester" for third-of-a-year
intervals (also called trimesters).  I've also heard of places that use a
fairly standard Sept-to-May calendar but call summer classes a third
"semester", even though it's shorter than their regular class sessions.

I don't think we should accept a patch along this line, because it assigns
a very specific meaning to "semester" that does not square all that well
with real-world usage.
        regards, tom lane

2015-08-15 0:55 GMT-03:00 Tom Lane <tgl@sss.pgh.pa.us>:
> I don't think we should accept a patch along this line, because it assigns
> a very specific meaning to "semester" that does not square all that well
> with real-world usage.

I agree that "semester" was not nice, the real meaning is "half" or
"halfyear" as mentioned in other messages. That's it, I was proposing
the extraction of the first or second half year using a specific word
like in:
   select extract('halfyear' from date '2015-08-15')); or   select date_trunc('halfyear', date '2015-08-15');

rather than trying to overloading `date_part` with a UDF or
calculating half like:
   select ceil(date_part('quarter', date '2015-08-15') / 2);


BTW, I feel that I have enough feedback to set patch as rejected.

Thank you for your time.
-- 
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br

On Fri, Aug 14, 2015 at 10:06:22PM -0300, Dickson S. Guedes wrote:
> 2015-08-14 21:32 GMT-03:00 Gavin Flower <GavinFlower@archidevsys.co.nz>:
> ...
> > So semesters don't appear to align with normal half year boundaries.
> 
> Interesting links, thanks!
> 
> Which sounds better for a native English: 'half', 'halfyear'?
> 
> For example:
> 
> > SELECT date_trunc('halfyear', current_date);
>        date_trunc
> ------------------------
>  2015-07-01 00:00:00-03
> (1 row)
> 
> Thanks!

As with, "quarter," we need to be careful about which type of "half"
we are defining.  For some financial calculations, a "month" is always
30 days, a "quarter," always three "months," i.e. 90 days, and a
"half" is six "months," or 180 days.

Yes, date math is crazy, and yes, we have to deal with it as it
exists.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate