Extracting fields from 'infinity'::TIMESTAMP[TZ]

Started by Vitaly Burovoyabout 10 years ago19 messages

vitaly.burovoy@gmail.com

about 10 years ago

1 attachment(s)

Hackers!

I'd like to raise a topic about extracting fields from infinite
timestamps, so much more that it is mentioned in the TODO list:
"Determine how to represent date/time field extraction on infinite
timestamps".

Currently extracting any field from 'infinity'::TIMESTAMP[TZ] gives
result "0" as a mark it has "special" input value.

The most confusing case is 'epoch' field: returning "0" from
"infinity" means the same thing as returning "0" from "1970-01-01+00".

Returning zero in most other cases is only slightly less confusing
(may be because for me they are less often used).
For example, what about "SELECT EXTRACT(DOW FROM TIMESTAMP
'Infinity')" with result 0, as if it is Sunday?
The same thing with fields: decade, hour, minute, seconds,
microseconds, milliseconds, timezone, timezone_hour, timezone_minute.
Also for "millennium" and "year" (with the note "Keep in mind there is
no 0 AD") current returning value is _between_ allowed values, but
disallowed.
http://www.postgresql.org/docs/9.5/static/functions-datetime.html

There was a discussion ended in nothing. It began at:
/messages/by-id/CA+mi_8bda-Fnev9iXeUbnqhVaCWzbYhHkWoxPQfBca9eDPpRMw@mail.gmail.com

Discussants agreed change is necessary, but couldn't decide what
behavior is preferred: throwing an error or returning NULL, NaN or +/-
infinity.

My thoughts about that cases:
* Throwing an error: prefer to avoid it according to
/messages/by-id/73A5666E-2D40-457E-9DFF-248895DB7FAF@gmail.com
* NULL: it is "absence of any value", i.e. it could be returned iff
input value is NULL (in the other case it is not better than returning
0).
* NaN: it could be returned if value is outside current axe (like
complex value), but it is not the case.

In a parallel discussion ("converting between infinity timestamp and
float8 (epoch)")
/messages/by-id/CADAkt-icuESH16uLOCXbR-dKpcvwtUJE4JWXnkdAjAAwP6j12g@mail.gmail.com
There was interesting thought to make difference between monotonic
values (century, decade, epoch, isoyear, millennium and year) and
oscillating values (day, dow, doy, hour, isodow, microseconds,
milliseconds, minute, month, quarter, second and week).
An argument is for monotonic values +/- infinity has a sense, but not
for oscillating ones.
But for oscillating values NULL was proposed, that (IMHO) is not a
good idea (see above).
I think changing current mark "input value is not finite" allows an
app layer (which knows which field it tries to fetch from
timestamp[tz]) to handle extracted value correctly. For oscillating
values there can be the same values as for monotonic values, because
you can't mix them up.
The end of the parallel discussion (with the most important thoughts)
at /messages/by-id/4EFCFD1C.8040001@archidevsys.co.nz

So I think +/- infinity is the best returning value for all fields.

The attached patch contains changes in timestamp_part and
timestamptz_part and tests for them.

I doubt whether it can be backpatched (according to team's rules) or
not, but the patch can be applied down to 9.2 without conflicts and
passes tests.
Unfortunately, on 9.1 proposed test fails because "SELECT
EXTRACT(EPOCH FROM DATE '1970-01-01')" gives "28800" instead of "0".
Before 9.2 it was time zone-related.
--
Best regards,
Vitaly Burovoy.

Attachments:

extract_from_infinite_timestamp-v1.patchapplication/octet-stream; name=extract_from_infinite_timestamp-v1.patchDownload

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 8fbb310..590ce3a 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4327,9 +4327,14 @@ timestamp_part(PG_FUNCTION_ARGS)
 	struct pg_tm tt,
 			   *tm = &tt;
 
-	if (TIMESTAMP_NOT_FINITE(timestamp))
+	if (TIMESTAMP_IS_NOBEGIN(timestamp))
 	{
-		result = 0;
+		result = -get_float8_infinity();
+		PG_RETURN_FLOAT8(result);
+	}
+	else if (TIMESTAMP_IS_NOEND(timestamp))
+	{
+		result = get_float8_infinity();
 		PG_RETURN_FLOAT8(result);
 	}
 
@@ -4538,9 +4543,14 @@ timestamptz_part(PG_FUNCTION_ARGS)
 	struct pg_tm tt,
 			   *tm = &tt;
 
-	if (TIMESTAMP_NOT_FINITE(timestamp))
+	if (TIMESTAMP_IS_NOBEGIN(timestamp))
 	{
-		result = 0;
+		result = -get_float8_infinity();
+		PG_RETURN_FLOAT8(result);
+	}
+	else if (TIMESTAMP_IS_NOEND(timestamp))
+	{
+		result = get_float8_infinity();
 		PG_RETURN_FLOAT8(result);
 	}
 
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index 8923f60..88ac4b5 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -900,6 +900,27 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
 --
 -- test extract!
 --
+-- epoch
+--
+SELECT EXTRACT(EPOCH FROM DATE '1970-01-01');     --  0
+ date_part 
+-----------
+         0
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM DATE 'infinity');       --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM DATE '-infinity');      -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
+--
 -- century
 --
 SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
@@ -962,6 +983,18 @@ SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True;     -- true
  t
 (1 row)
 
+SELECT EXTRACT(CENTURY FROM DATE 'infinity');      --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM DATE '-infinity');     -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
 --
 -- millennium
 --
@@ -1008,6 +1041,18 @@ SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE);         --  3
          3
 (1 row)
 
+SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity');      --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(MILLENNIUM FROM DATE '-infinity');     -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
 --
 -- decade
 --
@@ -1053,6 +1098,18 @@ SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); --  -2
         -2
 (1 row)
 
+SELECT EXTRACT(DECADE FROM DATE 'infinity');      --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(DECADE FROM DATE '-infinity');     -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
 --
 -- some other types:
 --
@@ -1069,6 +1126,37 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
         20
 (1 row)
 
+SELECT EXTRACT(CENTURY FROM TIMESTAMP 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM TIMESTAMP '-infinity');   -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
+-- on a timestamptz.
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00');     --  0
+ date_part 
+-----------
+         0
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity');          --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity');         -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
 -- on an interval
 SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');  -- 1
  date_part 
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index a62e92a..d465f8d 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -212,6 +212,12 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
 --
 -- test extract!
 --
+-- epoch
+--
+SELECT EXTRACT(EPOCH FROM DATE '1970-01-01');     --  0
+SELECT EXTRACT(EPOCH FROM DATE 'infinity');       --  Infinity
+SELECT EXTRACT(EPOCH FROM DATE '-infinity');      -- -Infinity
+--
 -- century
 --
 SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
@@ -224,6 +230,8 @@ SELECT EXTRACT(CENTURY FROM DATE '1901-01-01');    -- 20
 SELECT EXTRACT(CENTURY FROM DATE '2000-12-31');    -- 20
 SELECT EXTRACT(CENTURY FROM DATE '2001-01-01');    -- 21
 SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True;     -- true
+SELECT EXTRACT(CENTURY FROM DATE 'infinity');      --  Infinity
+SELECT EXTRACT(CENTURY FROM DATE '-infinity');     -- -Infinity
 --
 -- millennium
 --
@@ -235,6 +243,8 @@ SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31');    --  2
 SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01');    --  3
 -- next test to be fixed on the turn of the next millennium;-)
 SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE);         --  3
+SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity');      --  Infinity
+SELECT EXTRACT(MILLENNIUM FROM DATE '-infinity');     -- -Infinity
 --
 -- decade
 --
@@ -245,12 +255,20 @@ SELECT EXTRACT(DECADE FROM DATE '0001-01-01 BC'); --   0
 SELECT EXTRACT(DECADE FROM DATE '0002-12-31 BC'); --  -1
 SELECT EXTRACT(DECADE FROM DATE '0011-01-01 BC'); --  -1
 SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); --  -2
+SELECT EXTRACT(DECADE FROM DATE 'infinity');      --  Infinity
+SELECT EXTRACT(DECADE FROM DATE '-infinity');     -- -Infinity
 --
 -- some other types:
 --
 -- on a timestamp.
 SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True;       -- true
 SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
+SELECT EXTRACT(CENTURY FROM TIMESTAMP 'infinity');    --  Infinity
+SELECT EXTRACT(CENTURY FROM TIMESTAMP '-infinity');   -- -Infinity
+-- on a timestamptz.
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00');     --  0
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity');          --  Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity');         -- -Infinity
 -- on an interval
 SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');  -- 1
 SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');   -- 0

Robert Haas

robertmhaas@gmail.com

about 10 years ago

In reply to: Vitaly Burovoy (#1)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:

I'd like to raise a topic about extracting fields from infinite
timestamps, so much more that it is mentioned in the TODO list:
"Determine how to represent date/time field extraction on infinite
timestamps".

Currently extracting any field from 'infinity'::TIMESTAMP[TZ] gives
result "0" as a mark it has "special" input value.

The most confusing case is 'epoch' field: returning "0" from
"infinity" means the same thing as returning "0" from "1970-01-01+00".

Returning zero in most other cases is only slightly less confusing
(may be because for me they are less often used).
For example, what about "SELECT EXTRACT(DOW FROM TIMESTAMP
'Infinity')" with result 0, as if it is Sunday?
The same thing with fields: decade, hour, minute, seconds,
microseconds, milliseconds, timezone, timezone_hour, timezone_minute.
Also for "millennium" and "year" (with the note "Keep in mind there is
no 0 AD") current returning value is _between_ allowed values, but
disallowed.
http://www.postgresql.org/docs/9.5/static/functions-datetime.html

There was a discussion ended in nothing. It began at:
/messages/by-id/CA+mi_8bda-Fnev9iXeUbnqhVaCWzbYhHkWoxPQfBca9eDPpRMw@mail.gmail.com

Discussants agreed change is necessary, but couldn't decide what
behavior is preferred: throwing an error or returning NULL, NaN or +/-
infinity.

My thoughts about that cases:
* Throwing an error: prefer to avoid it according to
/messages/by-id/73A5666E-2D40-457E-9DFF-248895DB7FAF@gmail.com
* NULL: it is "absence of any value", i.e. it could be returned iff
input value is NULL (in the other case it is not better than returning
0).
* NaN: it could be returned if value is outside current axe (like
complex value), but it is not the case.

In a parallel discussion ("converting between infinity timestamp and
float8 (epoch)")
/messages/by-id/CADAkt-icuESH16uLOCXbR-dKpcvwtUJE4JWXnkdAjAAwP6j12g@mail.gmail.com
There was interesting thought to make difference between monotonic
values (century, decade, epoch, isoyear, millennium and year) and
oscillating values (day, dow, doy, hour, isodow, microseconds,
milliseconds, minute, month, quarter, second and week).
An argument is for monotonic values +/- infinity has a sense, but not
for oscillating ones.
But for oscillating values NULL was proposed, that (IMHO) is not a
good idea (see above).
I think changing current mark "input value is not finite" allows an
app layer (which knows which field it tries to fetch from
timestamp[tz]) to handle extracted value correctly. For oscillating
values there can be the same values as for monotonic values, because
you can't mix them up.
The end of the parallel discussion (with the most important thoughts)
at /messages/by-id/4EFCFD1C.8040001@archidevsys.co.nz

So I think +/- infinity is the best returning value for all fields.

The attached patch contains changes in timestamp_part and
timestamptz_part and tests for them.

I doubt whether it can be backpatched (according to team's rules) or
not, but the patch can be applied down to 9.2 without conflicts and
passes tests.
Unfortunately, on 9.1 proposed test fails because "SELECT
EXTRACT(EPOCH FROM DATE '1970-01-01')" gives "28800" instead of "0".
Before 9.2 it was time zone-related.

We're definitely not going to back-patch this. Let's tally up the
votes on that other thread:

Danielle Varrazzo: infinity
Bruce Momjian: infinity
Robert Haas: not sure we want to change anything, but if so let's
definitely NOT throw an error
Alvaro Herrera: infinity for epoch, but what about other things?
Brendan Jurd: infinity for epoch, error for other things
Tom Lane: infinity for epoch, error or NaN for other things
Josh Berkus: definitely change something, current behavior sucks

That doesn't seem like enough consensus to commit this patch, which
would change everything to +/-infinity. That particular choice
wouldn't bother me much, but it sounds like other people aren't sold.
I think we need to try to hash that out a little more rather than
rushing into a backward-incompatible change.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Kevin Grittner

kgrittn@ymail.com

about 10 years ago

In reply to: Robert Haas (#2)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:

I'd like to raise a topic about extracting fields from infinite
timestamps, so much more that it is mentioned in the TODO list:
"Determine how to represent date/time field extraction on infinite
timestamps".

Currently extracting any field from 'infinity'::TIMESTAMP[TZ] gives
result "0" as a mark it has "special" input value.

The most confusing case is 'epoch' field: returning "0" from
"infinity" means the same thing as returning "0" from "1970-01-01+00".

Returning zero in most other cases is only slightly less confusing
(may be because for me they are less often used).
For example, what about "SELECT EXTRACT(DOW FROM TIMESTAMP
'Infinity')" with result 0, as if it is Sunday?
The same thing with fields: decade, hour, minute, seconds,
microseconds, milliseconds, timezone, timezone_hour, timezone_minute.
Also for "millennium" and "year" (with the note "Keep in mind there is
no 0 AD") current returning value is _between_ allowed values, but
disallowed.

We're definitely not going to back-patch this. Let's tally up the
votes on that other thread:

Danielle Varrazzo: infinity
Bruce Momjian: infinity
Robert Haas: not sure we want to change anything, but if so let's
definitely NOT throw an error
Alvaro Herrera: infinity for epoch, but what about other things?
Brendan Jurd: infinity for epoch, error for other things
Tom Lane: infinity for epoch, error or NaN for other things
Josh Berkus: definitely change something, current behavior sucks

That doesn't seem like enough consensus to commit this patch, which
would change everything to +/-infinity. That particular choice
wouldn't bother me much, but it sounds like other people aren't sold.
I think we need to try to hash that out a little more rather than
rushing into a backward-incompatible change.

I agree that none of this should be back-patched.

I agree that a timestamp[tz] of infinity should yield infinity for
epoch.

My first choice for other things would be NaN, but throwing an
error instead would be OK.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane

tgl@sss.pgh.pa.us

about 10 years ago

In reply to: Kevin Grittner (#3)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

Kevin Grittner <kgrittn@ymail.com> writes:

On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmhaas@gmail.com> wrote:

That doesn't seem like enough consensus to commit this patch, which
would change everything to +/-infinity. That particular choice
wouldn't bother me much, but it sounds like other people aren't sold.
I think we need to try to hash that out a little more rather than
rushing into a backward-incompatible change.

I agree that none of this should be back-patched.

Definitely.

I agree that a timestamp[tz] of infinity should yield infinity for
epoch.

I think everybody is sold on that much.

My first choice for other things would be NaN, but throwing an
error instead would be OK.

Since the function hasn't thrown error for such cases in the past, making
it do so now would likely break applications. More than once, we've
had to modify functions to avoid throwing errors so that you don't get
incidental errors when blindly applying a function to all entries in a
column. I think going in the opposite direction would elicit protests.

I could see using NaN except for one thing: it'd mean injecting a rather
fundamental dependence on IEEE math into a basic function definition. You
can be just about 100% certain that if the SQL committee ever addresses
this case, it won't be with NaN.

What about returning NULL for the ill-defined cases? That seems to
comport with SQL's notion of NULL as "unknown/undefined".

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

Steve Crawford

scrawford@pinpointresearch.com

about 10 years ago

In reply to: Kevin Grittner (#3)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

I was unaware that we had +- infinity for numeric.

select pg_typeof(extract(epoch from current_date));
pg_typeof
------------------
double precision

Given that null is a "special value that is used to indicate the absence of
any data value" and that attributes like month or day-of-week will have no
value for a date of infinity I'd be OK with returning null.

I suppose the real question is what return value will cause the smallest
amount of breakage and surprising results. Throwing an error will
definitely break legit queries.

Cheers,
Steve

On Mon, Nov 9, 2015 at 8:22 AM, Kevin Grittner <kgrittn@ymail.com> wrote:

Show quoted text

On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmhaas@gmail.com>
wrote:

On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy <vitaly.burovoy@gmail.com>

wrote:

I'd like to raise a topic about extracting fields from infinite
timestamps, so much more that it is mentioned in the TODO list:
"Determine how to represent date/time field extraction on infinite
timestamps".

Currently extracting any field from 'infinity'::TIMESTAMP[TZ] gives
result "0" as a mark it has "special" input value.

The most confusing case is 'epoch' field: returning "0" from
"infinity" means the same thing as returning "0" from "1970-01-01+00".

Returning zero in most other cases is only slightly less confusing
(may be because for me they are less often used).
For example, what about "SELECT EXTRACT(DOW FROM TIMESTAMP
'Infinity')" with result 0, as if it is Sunday?
The same thing with fields: decade, hour, minute, seconds,
microseconds, milliseconds, timezone, timezone_hour, timezone_minute.
Also for "millennium" and "year" (with the note "Keep in mind there is
no 0 AD") current returning value is _between_ allowed values, but
disallowed.

We're definitely not going to back-patch this. Let's tally up the
votes on that other thread:

Danielle Varrazzo: infinity
Bruce Momjian: infinity
Robert Haas: not sure we want to change anything, but if so let's
definitely NOT throw an error
Alvaro Herrera: infinity for epoch, but what about other things?
Brendan Jurd: infinity for epoch, error for other things
Tom Lane: infinity for epoch, error or NaN for other things
Josh Berkus: definitely change something, current behavior sucks

That doesn't seem like enough consensus to commit this patch, which
would change everything to +/-infinity. That particular choice
wouldn't bother me much, but it sounds like other people aren't sold.
I think we need to try to hash that out a little more rather than
rushing into a backward-incompatible change.

I agree that none of this should be back-patched.

I agree that a timestamp[tz] of infinity should yield infinity for
epoch.

My first choice for other things would be NaN, but throwing an
error instead would be OK.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Kevin Grittner

kgrittn@ymail.com

about 10 years ago

In reply to: Steve Crawford (#5)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On Mon, Nov 9, 2015 at 8:22 AM, Kevin Grittner <kgrittn@ymail.com> wrote:

My first choice for other things would be NaN, but throwing an
error instead would be OK.

On Monday, November 9, 2015 10:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

What about returning NULL for the ill-defined cases? That seems
to comport with SQL's notion of NULL as "unknown/undefined".

On Monday, November 9, 2015 10:44 AM, Steve Crawford <scrawford@pinpointresearch.com> wrote:

Given that null is a "special value that is used to indicate the
absence of any data value" and that attributes like month or
day-of-week will have no value for a date of infinity I'd be OK
with returning null.

NULL seens clearly better than NaN or an error; I wish that had
occurred to me before I posted.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Torsten Zuehlsdorff

mailinglists@toco-domains.de

about 10 years ago

In reply to: Tom Lane (#4)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 09.11.2015 17:41, Tom Lane wrote:

Kevin Grittner <kgrittn@ymail.com> writes:

On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmhaas@gmail.com> wrote:

That doesn't seem like enough consensus to commit this patch, which
would change everything to +/-infinity. That particular choice
wouldn't bother me much, but it sounds like other people aren't sold.
I think we need to try to hash that out a little more rather than
rushing into a backward-incompatible change.

I agree that none of this should be back-patched.

Definitely.

I agree that a timestamp[tz] of infinity should yield infinity for
epoch.

I think everybody is sold on that much.

My first choice for other things would be NaN, but throwing an
error instead would be OK.

Since the function hasn't thrown error for such cases in the past, making
it do so now would likely break applications. More than once, we've
had to modify functions to avoid throwing errors so that you don't get
incidental errors when blindly applying a function to all entries in a
column. I think going in the opposite direction would elicit protests.

An error will also break legit SQL statements.

I could see using NaN except for one thing: it'd mean injecting a rather
fundamental dependence on IEEE math into a basic function definition. You
can be just about 100% certain that if the SQL committee ever addresses
this case, it won't be with NaN.

ACK.

What about returning NULL for the ill-defined cases? That seems to
comport with SQL's notion of NULL as "unknown/undefined".

This is the first i would expect in such a case.

+ 1 for NULL.

Greetings,
Torsten

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Vitaly Burovoy

vitaly.burovoy@gmail.com

about 10 years ago

In reply to: Robert Haas (#2)

1 attachment(s)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 11/9/15, Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy <vitaly.burovoy@gmail.com>
wrote:

I'd like to raise a topic about extracting fields from infinite
timestamps, so much more that it is mentioned in the TODO list:
"Determine how to represent date/time field extraction on infinite
timestamps".

Currently extracting any field from 'infinity'::TIMESTAMP[TZ] gives
result "0" as a mark it has "special" input value.

The most confusing case is 'epoch' field: returning "0" from
"infinity" means the same thing as returning "0" from "1970-01-01+00".

Returning zero in most other cases is only slightly less confusing
(may be because for me they are less often used).
For example, what about "SELECT EXTRACT(DOW FROM TIMESTAMP
'Infinity')" with result 0, as if it is Sunday?
The same thing with fields: decade, hour, minute, seconds,
microseconds, milliseconds, timezone, timezone_hour, timezone_minute.
Also for "millennium" and "year" (with the note "Keep in mind there is
no 0 AD") current returning value is _between_ allowed values, but
disallowed.
http://www.postgresql.org/docs/9.5/static/functions-datetime.html

There was a discussion ended in nothing. It began at:
/messages/by-id/CA+mi_8bda-Fnev9iXeUbnqhVaCWzbYhHkWoxPQfBca9eDPpRMw@mail.gmail.com

Discussants agreed change is necessary, but couldn't decide what
behavior is preferred: throwing an error or returning NULL, NaN or +/-
infinity.

My thoughts about that cases:
* Throwing an error: prefer to avoid it according to
/messages/by-id/73A5666E-2D40-457E-9DFF-248895DB7FAF@gmail.com
* NULL: it is "absence of any value", i.e. it could be returned iff
input value is NULL (in the other case it is not better than returning
0).
* NaN: it could be returned if value is outside current axe (like
complex value), but it is not the case.

In a parallel discussion ("converting between infinity timestamp and
float8 (epoch)")
/messages/by-id/CADAkt-icuESH16uLOCXbR-dKpcvwtUJE4JWXnkdAjAAwP6j12g@mail.gmail.com
There was interesting thought to make difference between monotonic
values (century, decade, epoch, isoyear, millennium and year) and
oscillating values (day, dow, doy, hour, isodow, microseconds,
milliseconds, minute, month, quarter, second and week).
An argument is for monotonic values +/- infinity has a sense, but not
for oscillating ones.
But for oscillating values NULL was proposed, that (IMHO) is not a
good idea (see above).
I think changing current mark "input value is not finite" allows an
app layer (which knows which field it tries to fetch from
timestamp[tz]) to handle extracted value correctly. For oscillating
values there can be the same values as for monotonic values, because
you can't mix them up.
The end of the parallel discussion (with the most important thoughts)
at
/messages/by-id/4EFCFD1C.8040001@archidevsys.co.nz

So I think +/- infinity is the best returning value for all fields.

The attached patch contains changes in timestamp_part and
timestamptz_part and tests for them.

I doubt whether it can be backpatched (according to team's rules) or
not, but the patch can be applied down to 9.2 without conflicts and
passes tests.
Unfortunately, on 9.1 proposed test fails because "SELECT
EXTRACT(EPOCH FROM DATE '1970-01-01')" gives "28800" instead of "0".
Before 9.2 it was time zone-related.

We're definitely not going to back-patch this. Let's tally up the
votes on that other thread:

Danielle Varrazzo: infinity
Bruce Momjian: infinity
Robert Haas: not sure we want to change anything, but if so let's
definitely NOT throw an error
Alvaro Herrera: infinity for epoch, but what about other things?
Brendan Jurd: infinity for epoch, error for other things
Tom Lane: infinity for epoch, error or NaN for other things
Josh Berkus: definitely change something, current behavior sucks

That doesn't seem like enough consensus to commit this patch, which
would change everything to +/-infinity. That particular choice
wouldn't bother me much, but it sounds like other people aren't sold.
I think we need to try to hash that out a little more rather than
rushing into a backward-incompatible change.

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

I apologize for the late answer: I was very sick last week.

Kevin Grittner | | + | |
Tom Lane | | + | - | -
Steve Crawford | | + | |
Torsten Zuehlsdorff | | + | |
Total: 2 4 0 0

Majority of the votes for NULL for "other things" except epoch.
Nobody answers about differences between monotonic and oscillating values.

I suppose behavior of monotonic values (julian, century, decade,
isoyear, millennium and year) should be the same as for epoch (which
obviously also monotonic value).
Proposed patch has that behavior: +/-infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

P.S.: I've just found out there is no explanation of "JULIAN" unit in
the documentation of extracting at all. In the other parts of the
documentation there is only history of Julian Date system, inputs and
formatting.
--
Best regards,
Vitaly Burovoy

Attachments:

extract_from_infinite_timestamp-v2.patchapplication/octet-stream; name=extract_from_infinite_timestamp-v2.patchDownload

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 8fbb310..006999f 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4311,6 +4311,69 @@ date2isoyearday(int year, int mon, int mday)
 	return date2j(year, mon, mday) - isoweek2j(date2isoyear(year, mon, mday), 1) + 1;
 }
 
+/*
+ * HandleNonFiniteTimestampTzPart
+ *
+ *	Used by timestamp_part and timestamptz_part when extracting from non-finite timestamp[tz].
+ *	For monotonic units sets result to +/-Inf and returns true.
+ *	For oscillating ones leaves result as is and returns false.
+ *	Return value is a flag whether +/-Inf from result must be returned or NULL.
+ */
+static bool
+HandleNonFiniteTimestampTzPart(int type, int unit, char* lowunits,
+							   float8 *result, bool isNegative)
+{
+	/*
+	 * Units are in the same order as in timestamp_part and timestamptz_part.
+	 * Monotonic units are moved to a separate block (but in the same order).
+	*/
+	if ((type != UNITS) && (type != RESERV))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("timestamp units \"%s\" not recognized", lowunits)));
+
+	switch (unit)
+	{
+		/* Oscillating units */
+		case DTK_MICROSEC:
+		case DTK_MILLISEC:
+		case DTK_SECOND:
+		case DTK_MINUTE:
+		case DTK_HOUR:
+		case DTK_DAY:
+		case DTK_MONTH:
+		case DTK_QUARTER:
+		case DTK_WEEK:
+		/* Monotonic units between WEEK and DOW are moved to the end of switch */
+		case DTK_DOW:
+		case DTK_ISODOW:
+		case DTK_DOY:
+		case DTK_TZ:
+		case DTK_TZ_MINUTE:
+		case DTK_TZ_HOUR:
+			return false;
+
+		/* Monotonic units */
+		case DTK_YEAR:
+		case DTK_DECADE:
+		case DTK_CENTURY:
+		case DTK_MILLENNIUM:
+		case DTK_JULIAN:
+		case DTK_ISOYEAR:
+		case DTK_EPOCH:
+			*result = get_float8_infinity();
+			if (isNegative)
+				*result = -(*result);
+			return true;
+
+		default:
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("timestamp units \"%s\" not supported",
+							lowunits)));
+	}
+}
+
 /* timestamp_part()
  * Extract specified field from timestamp.
  */
@@ -4327,12 +4390,6 @@ timestamp_part(PG_FUNCTION_ARGS)
 	struct pg_tm tt,
 			   *tm = &tt;
 
-	if (TIMESTAMP_NOT_FINITE(timestamp))
-	{
-		result = 0;
-		PG_RETURN_FLOAT8(result);
-	}
-
 	lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
 											VARSIZE_ANY_EXHDR(units),
 											false);
@@ -4341,6 +4398,16 @@ timestamp_part(PG_FUNCTION_ARGS)
 	if (type == UNKNOWN_FIELD)
 		type = DecodeSpecial(0, lowunits, &val);
 
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+	{
+		if (HandleNonFiniteTimestampTzPart(type, val, lowunits,
+										   &result,
+										   TIMESTAMP_IS_NOBEGIN(timestamp)))
+			PG_RETURN_FLOAT8(result);
+		else
+			PG_RETURN_NULL();
+	}
+
 	if (type == UNITS)
 	{
 		if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
@@ -4538,12 +4605,6 @@ timestamptz_part(PG_FUNCTION_ARGS)
 	struct pg_tm tt,
 			   *tm = &tt;
 
-	if (TIMESTAMP_NOT_FINITE(timestamp))
-	{
-		result = 0;
-		PG_RETURN_FLOAT8(result);
-	}
-
 	lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
 											VARSIZE_ANY_EXHDR(units),
 											false);
@@ -4552,6 +4613,16 @@ timestamptz_part(PG_FUNCTION_ARGS)
 	if (type == UNKNOWN_FIELD)
 		type = DecodeSpecial(0, lowunits, &val);
 
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+	{
+		if (HandleNonFiniteTimestampTzPart(type, val, lowunits,
+										   &result,
+										   TIMESTAMP_IS_NOBEGIN(timestamp)))
+			PG_RETURN_FLOAT8(result);
+		else
+			PG_RETURN_NULL();
+	}
+
 	if (type == UNITS)
 	{
 		if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index 8923f60..56c5520 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -900,6 +900,27 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
 --
 -- test extract!
 --
+-- epoch
+--
+SELECT EXTRACT(EPOCH FROM DATE        '1970-01-01');     --  0
+ date_part 
+-----------
+         0
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   '1970-01-01');     --  0
+ date_part 
+-----------
+         0
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00');  --  0
+ date_part 
+-----------
+         0
+(1 row)
+
+--
 -- century
 --
 SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
@@ -1184,6 +1205,227 @@ select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
  f        | f        | t
 (1 row)
 
+--
+-- oscillating fields from non-finite date/timestamptz:
+--
+SELECT EXTRACT(HOUR FROM DATE 'infinity');      -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM DATE '-infinity');     -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIMESTAMP   'infinity');      -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIMESTAMP   '-infinity');     -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIMESTAMPTZ 'infinity');      -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '-infinity');     -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+-- all possible fields
+SELECT EXTRACT(MICROSECONDS  FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(MILLISECONDS  FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(SECOND        FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(MINUTE        FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR          FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(DAY           FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(MONTH         FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(QUARTER       FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(WEEK          FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(DOW           FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(ISODOW        FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(DOY           FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(TIMEZONE      FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(TIMEZONE_M    FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(TIMEZONE_H    FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+--
+-- monotonic fields from non-finite date/timestamptz:
+--
+SELECT EXTRACT(EPOCH FROM DATE 'infinity');         --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM DATE '-infinity');        -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   'infinity');  --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   '-infinity'); -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity');  --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity'); -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
+-- all possible fields
+SELECT EXTRACT(YEAR       FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(DECADE     FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(CENTURY    FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(JULIAN     FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(ISOYEAR    FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH      FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+--
+-- wrong fields from non-finite date:
+--
+SELECT EXTRACT(MICROSEC  FROM DATE 'infinity');     -- ERROR:  timestamp units "microsec" not recognized
+ERROR:  timestamp units "microsec" not recognized
+CONTEXT:  SQL function "date_part" statement 1
+SELECT EXTRACT(UNDEFINED FROM DATE 'infinity');     -- ERROR:  timestamp units "undefined" not supported
+ERROR:  timestamp units "undefined" not supported
+CONTEXT:  SQL function "date_part" statement 1
 -- test constructors
 select make_date(2013, 7, 15);
  make_date  
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index a62e92a..e40b4c4 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -212,6 +212,12 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
 --
 -- test extract!
 --
+-- epoch
+--
+SELECT EXTRACT(EPOCH FROM DATE        '1970-01-01');     --  0
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   '1970-01-01');     --  0
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00');  --  0
+--
 -- century
 --
 SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
@@ -276,6 +282,53 @@ 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);
+--
+-- oscillating fields from non-finite date/timestamptz:
+--
+SELECT EXTRACT(HOUR FROM DATE 'infinity');      -- NULL
+SELECT EXTRACT(HOUR FROM DATE '-infinity');     -- NULL
+SELECT EXTRACT(HOUR FROM TIMESTAMP   'infinity');      -- NULL
+SELECT EXTRACT(HOUR FROM TIMESTAMP   '-infinity');     -- NULL
+SELECT EXTRACT(HOUR FROM TIMESTAMPTZ 'infinity');      -- NULL
+SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '-infinity');     -- NULL
+-- all possible fields
+SELECT EXTRACT(MICROSECONDS  FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(MILLISECONDS  FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(SECOND        FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(MINUTE        FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(HOUR          FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(DAY           FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(MONTH         FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(QUARTER       FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(WEEK          FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(DOW           FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(ISODOW        FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(DOY           FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(TIMEZONE      FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(TIMEZONE_M    FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(TIMEZONE_H    FROM DATE 'infinity');    -- NULL
+--
+-- monotonic fields from non-finite date/timestamptz:
+--
+SELECT EXTRACT(EPOCH FROM DATE 'infinity');         --  Infinity
+SELECT EXTRACT(EPOCH FROM DATE '-infinity');        -- -Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   'infinity');  --  Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   '-infinity'); -- -Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity');  --  Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity'); -- -Infinity
+-- all possible fields
+SELECT EXTRACT(YEAR       FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(DECADE     FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(CENTURY    FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(JULIAN     FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(ISOYEAR    FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(EPOCH      FROM DATE 'infinity');    --  Infinity
+--
+-- wrong fields from non-finite date:
+--
+SELECT EXTRACT(MICROSEC  FROM DATE 'infinity');     -- ERROR:  timestamp units "microsec" not recognized
+SELECT EXTRACT(UNDEFINED FROM DATE 'infinity');     -- ERROR:  timestamp units "undefined" not supported
 
 -- test constructors
 select make_date(2013, 7, 15);

Jim Nasby

Jim.Nasby@BlueTreble.com

about 10 years ago

In reply to: Vitaly Burovoy (#8)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 11/17/15 2:09 AM, Vitaly Burovoy wrote:

I suppose behavior of monotonic values (julian, century, decade,
isoyear, millennium and year) should be the same as for epoch (which
obviously also monotonic value).
Proposed patch has that behavior: ±infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

What's the logic behind NULL here? Infinity is infinity, whether it's
minutes or years. It's absolutely NOT the same thing as a NULL
timestamp. I don't see why the normal constraint of minute < 60 should
apply here; infinity isn't a normal number.

My specific fear is that now people will have to do a bunch of IF
timestamp IS NOT NULL THEN ... to get the behavior they need.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#10

Tom Lane

tgl@sss.pgh.pa.us

about 10 years ago

In reply to: Vitaly Burovoy (#8)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

Vitaly Burovoy <vitaly.burovoy@gmail.com> writes:

I suppose behavior of monotonic values (julian, century, decade,
isoyear, millennium and year) should be the same as for epoch (which
obviously also monotonic value).
Proposed patch has that behavior: +/-infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

Works for me.

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

#11

Robert Haas

robertmhaas@gmail.com

about 10 years ago

In reply to: Tom Lane (#10)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On Tue, Nov 17, 2015 at 10:57 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Vitaly Burovoy <vitaly.burovoy@gmail.com> writes:

I suppose behavior of monotonic values (julian, century, decade,
isoyear, millennium and year) should be the same as for epoch (which
obviously also monotonic value).
Proposed patch has that behavior: +/-infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

Works for me.

Same here.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#12

Tom Lane

tgl@sss.pgh.pa.us

about 10 years ago

In reply to: Jim Nasby (#9)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:

On 11/17/15 2:09 AM, Vitaly Burovoy wrote:

Proposed patch has that behavior: ±infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

What's the logic behind NULL here? Infinity is infinity, whether it's
minutes or years.

Didn't you follow the upthread discussion? Fields such as "minutes"
are cyclic, so it's impossible to say either that they converge to
a defined limit or diverge to infinity as x increases. NULL, in the
sense of "unknown", seems like a reasonable representation of that.
Infinity doesn't.

My specific fear is that now people will have to do a bunch of IF
timestamp IS NOT NULL THEN ... to get the behavior they need.

Considering that the old behavior is to return zero, and we've had
relatively few complaints about that, I doubt very many people are
going to care.

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

#13

Torsten Zühlsdorff

mailinglists@toco-domains.de

about 10 years ago

In reply to: Vitaly Burovoy (#8)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 17.11.2015 09:09, Vitaly Burovoy wrote:

I suppose behavior of monotonic values (julian, century, decade,
isoyear, millennium and year) should be the same as for epoch (which
obviously also monotonic value).
Proposed patch has that behavior: +/-infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

This works for me.

Greetings,
Torsten

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#14

Alvaro Herrera

alvherre@2ndquadrant.com

about 10 years ago

In reply to: Vitaly Burovoy (#8)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

Vitaly Burovoy wrote:

Majority of the votes for NULL for "other things" except epoch.
Nobody answers about differences between monotonic and oscillating values.

I suppose behavior of monotonic values (julian, century, decade,
isoyear, millennium and year) should be the same as for epoch (which
obviously also monotonic value).
Proposed patch has that behavior: +/-infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

It seems we got majority approval on the design of this patch, and no
disagreement; the last submitted version appears to implement that.
There's no documentation change in the patch though. I'm marking it as
Waiting on Author; please resubmit with necessary doc changes.

Thanks,

--
ï¿½lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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

#15

Vitaly Burovoy

vitaly.burovoy@gmail.com

about 10 years ago

In reply to: Alvaro Herrera (#14)

1 attachment(s)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 1/4/16, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

Vitaly Burovoy wrote:

Majority of the votes for NULL for "other things" except epoch.
Nobody answers about differences between monotonic and oscillating
values.

I suppose behavior of monotonic values (julian, century, decade,
isoyear, millennium and year) should be the same as for epoch (which
obviously also monotonic value).
Proposed patch has that behavior: +/-infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

It seems we got majority approval on the design of this patch, and no
disagreement; the last submitted version appears to implement that.
There's no documentation change in the patch though. I'm marking it as
Waiting on Author; please resubmit with necessary doc changes.

Thanks,

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Thank you!
Version 3 of the patch with touched documentation in the attachment.

I decided to mark it as a note, because that separation
(monotonic/oscillation fields) is not obvious and for most values the
function "extract" works as expected (e.g. does not give an error)
until special values are (casually?) passed.
--
Best regards,
Vitaly Burovoy

Attachments:

extract_from_infinite_timestamp-v3.patchapplication/octet-stream; name=extract_from_infinite_timestamp-v3.patchDownload

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2d26896..04a3009 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -7514,6 +7514,29 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
     display, see <xref linkend="functions-formatting">.
    </para>
 
+   <note>
+    <para>
+     Extracting from infinite values differs for monotonic and oscillating
+     fields.
+    </para>
+    <para>
+     For monotonic ones (<literal>epoch</>, <literal>julian</>,
+     <literal>century</>, <literal>decade</>, <literal>isoyear</>,
+     <literal>millennium</> and <literal>year</>) appropriate +/-Infinity is
+     returned, for all other (oscillating) fields null is returned:
+    </para>
+
+<screen>
+SELECT EXTRACT(EPOCH FROM TIMESTAMP 'Infinity');
+<lineannotation>Result: </lineannotation><computeroutput>Infinity</computeroutput>
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '-Infinity');
+<lineannotation>Result: </lineannotation><computeroutput>-Infinity</computeroutput>
+SELECT EXTRACT(DAY FROM TIMESTAMP 'Infinity') IS NULL AND
+       EXTRACT(DAY FROM TIMESTAMP '-Infinity') IS NULL;
+<lineannotation>Result: </lineannotation><computeroutput>true</computeroutput>
+</screen>
+   </note>
+
    <para>
     The <function>date_part</function> function is modeled on the traditional
     <productname>Ingres</productname> equivalent to the
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 6871092..8e4e7f6 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4311,6 +4311,69 @@ date2isoyearday(int year, int mon, int mday)
 	return date2j(year, mon, mday) - isoweek2j(date2isoyear(year, mon, mday), 1) + 1;
 }
 
+/*
+ * HandleNonFiniteTimestampTzPart
+ *
+ *	Used by timestamp_part and timestamptz_part when extracting from non-finite timestamp[tz].
+ *	For monotonic units sets result to +/-Inf and returns true.
+ *	For oscillating ones leaves result as is and returns false.
+ *	Return value is a flag whether +/-Inf from result must be returned or NULL.
+ */
+static bool
+HandleNonFiniteTimestampTzPart(int type, int unit, char* lowunits,
+							   float8 *result, bool isNegative)
+{
+	/*
+	 * Units are in the same order as in timestamp_part and timestamptz_part.
+	 * Monotonic units are moved to a separate block (but in the same order).
+	*/
+	if ((type != UNITS) && (type != RESERV))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("timestamp units \"%s\" not recognized", lowunits)));
+
+	switch (unit)
+	{
+		/* Oscillating units */
+		case DTK_MICROSEC:
+		case DTK_MILLISEC:
+		case DTK_SECOND:
+		case DTK_MINUTE:
+		case DTK_HOUR:
+		case DTK_DAY:
+		case DTK_MONTH:
+		case DTK_QUARTER:
+		case DTK_WEEK:
+		/* Monotonic units between WEEK and DOW are moved to the end of switch */
+		case DTK_DOW:
+		case DTK_ISODOW:
+		case DTK_DOY:
+		case DTK_TZ:
+		case DTK_TZ_MINUTE:
+		case DTK_TZ_HOUR:
+			return false;
+
+		/* Monotonic units */
+		case DTK_YEAR:
+		case DTK_DECADE:
+		case DTK_CENTURY:
+		case DTK_MILLENNIUM:
+		case DTK_JULIAN:
+		case DTK_ISOYEAR:
+		case DTK_EPOCH:
+			*result = get_float8_infinity();
+			if (isNegative)
+				*result = -(*result);
+			return true;
+
+		default:
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("timestamp units \"%s\" not supported",
+							lowunits)));
+	}
+}
+
 /* timestamp_part()
  * Extract specified field from timestamp.
  */
@@ -4327,12 +4390,6 @@ timestamp_part(PG_FUNCTION_ARGS)
 	struct pg_tm tt,
 			   *tm = &tt;
 
-	if (TIMESTAMP_NOT_FINITE(timestamp))
-	{
-		result = 0;
-		PG_RETURN_FLOAT8(result);
-	}
-
 	lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
 											VARSIZE_ANY_EXHDR(units),
 											false);
@@ -4341,6 +4398,16 @@ timestamp_part(PG_FUNCTION_ARGS)
 	if (type == UNKNOWN_FIELD)
 		type = DecodeSpecial(0, lowunits, &val);
 
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+	{
+		if (HandleNonFiniteTimestampTzPart(type, val, lowunits,
+										   &result,
+										   TIMESTAMP_IS_NOBEGIN(timestamp)))
+			PG_RETURN_FLOAT8(result);
+		else
+			PG_RETURN_NULL();
+	}
+
 	if (type == UNITS)
 	{
 		if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
@@ -4538,12 +4605,6 @@ timestamptz_part(PG_FUNCTION_ARGS)
 	struct pg_tm tt,
 			   *tm = &tt;
 
-	if (TIMESTAMP_NOT_FINITE(timestamp))
-	{
-		result = 0;
-		PG_RETURN_FLOAT8(result);
-	}
-
 	lowunits = downcase_truncate_identifier(VARDATA_ANY(units),
 											VARSIZE_ANY_EXHDR(units),
 											false);
@@ -4552,6 +4613,16 @@ timestamptz_part(PG_FUNCTION_ARGS)
 	if (type == UNKNOWN_FIELD)
 		type = DecodeSpecial(0, lowunits, &val);
 
+	if (TIMESTAMP_NOT_FINITE(timestamp))
+	{
+		if (HandleNonFiniteTimestampTzPart(type, val, lowunits,
+										   &result,
+										   TIMESTAMP_IS_NOBEGIN(timestamp)))
+			PG_RETURN_FLOAT8(result);
+		else
+			PG_RETURN_NULL();
+	}
+
 	if (type == UNITS)
 	{
 		if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index 8923f60..56c5520 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -900,6 +900,27 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
 --
 -- test extract!
 --
+-- epoch
+--
+SELECT EXTRACT(EPOCH FROM DATE        '1970-01-01');     --  0
+ date_part 
+-----------
+         0
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   '1970-01-01');     --  0
+ date_part 
+-----------
+         0
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00');  --  0
+ date_part 
+-----------
+         0
+(1 row)
+
+--
 -- century
 --
 SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
@@ -1184,6 +1205,227 @@ select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
  f        | f        | t
 (1 row)
 
+--
+-- oscillating fields from non-finite date/timestamptz:
+--
+SELECT EXTRACT(HOUR FROM DATE 'infinity');      -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM DATE '-infinity');     -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIMESTAMP   'infinity');      -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIMESTAMP   '-infinity');     -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIMESTAMPTZ 'infinity');      -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '-infinity');     -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+-- all possible fields
+SELECT EXTRACT(MICROSECONDS  FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(MILLISECONDS  FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(SECOND        FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(MINUTE        FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(HOUR          FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(DAY           FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(MONTH         FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(QUARTER       FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(WEEK          FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(DOW           FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(ISODOW        FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(DOY           FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(TIMEZONE      FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(TIMEZONE_M    FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+SELECT EXTRACT(TIMEZONE_H    FROM DATE 'infinity');    -- NULL
+ date_part 
+-----------
+          
+(1 row)
+
+--
+-- monotonic fields from non-finite date/timestamptz:
+--
+SELECT EXTRACT(EPOCH FROM DATE 'infinity');         --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM DATE '-infinity');        -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   'infinity');  --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   '-infinity'); -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity');  --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity'); -- -Infinity
+ date_part 
+-----------
+ -Infinity
+(1 row)
+
+-- all possible fields
+SELECT EXTRACT(YEAR       FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(DECADE     FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(CENTURY    FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(JULIAN     FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(ISOYEAR    FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+SELECT EXTRACT(EPOCH      FROM DATE 'infinity');    --  Infinity
+ date_part 
+-----------
+  Infinity
+(1 row)
+
+--
+-- wrong fields from non-finite date:
+--
+SELECT EXTRACT(MICROSEC  FROM DATE 'infinity');     -- ERROR:  timestamp units "microsec" not recognized
+ERROR:  timestamp units "microsec" not recognized
+CONTEXT:  SQL function "date_part" statement 1
+SELECT EXTRACT(UNDEFINED FROM DATE 'infinity');     -- ERROR:  timestamp units "undefined" not supported
+ERROR:  timestamp units "undefined" not supported
+CONTEXT:  SQL function "date_part" statement 1
 -- test constructors
 select make_date(2013, 7, 15);
  make_date  
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index a62e92a..e40b4c4 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -212,6 +212,12 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
 --
 -- test extract!
 --
+-- epoch
+--
+SELECT EXTRACT(EPOCH FROM DATE        '1970-01-01');     --  0
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   '1970-01-01');     --  0
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00');  --  0
+--
 -- century
 --
 SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
@@ -276,6 +282,53 @@ 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);
+--
+-- oscillating fields from non-finite date/timestamptz:
+--
+SELECT EXTRACT(HOUR FROM DATE 'infinity');      -- NULL
+SELECT EXTRACT(HOUR FROM DATE '-infinity');     -- NULL
+SELECT EXTRACT(HOUR FROM TIMESTAMP   'infinity');      -- NULL
+SELECT EXTRACT(HOUR FROM TIMESTAMP   '-infinity');     -- NULL
+SELECT EXTRACT(HOUR FROM TIMESTAMPTZ 'infinity');      -- NULL
+SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '-infinity');     -- NULL
+-- all possible fields
+SELECT EXTRACT(MICROSECONDS  FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(MILLISECONDS  FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(SECOND        FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(MINUTE        FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(HOUR          FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(DAY           FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(MONTH         FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(QUARTER       FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(WEEK          FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(DOW           FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(ISODOW        FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(DOY           FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(TIMEZONE      FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(TIMEZONE_M    FROM DATE 'infinity');    -- NULL
+SELECT EXTRACT(TIMEZONE_H    FROM DATE 'infinity');    -- NULL
+--
+-- monotonic fields from non-finite date/timestamptz:
+--
+SELECT EXTRACT(EPOCH FROM DATE 'infinity');         --  Infinity
+SELECT EXTRACT(EPOCH FROM DATE '-infinity');        -- -Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   'infinity');  --  Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMP   '-infinity'); -- -Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity');  --  Infinity
+SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity'); -- -Infinity
+-- all possible fields
+SELECT EXTRACT(YEAR       FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(DECADE     FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(CENTURY    FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(JULIAN     FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(ISOYEAR    FROM DATE 'infinity');    --  Infinity
+SELECT EXTRACT(EPOCH      FROM DATE 'infinity');    --  Infinity
+--
+-- wrong fields from non-finite date:
+--
+SELECT EXTRACT(MICROSEC  FROM DATE 'infinity');     -- ERROR:  timestamp units "microsec" not recognized
+SELECT EXTRACT(UNDEFINED FROM DATE 'infinity');     -- ERROR:  timestamp units "undefined" not supported
 
 -- test constructors
 select make_date(2013, 7, 15);

#16

Vik Fearing

vik@2ndquadrant.fr

almost 10 years ago

In reply to: Vitaly Burovoy (#15)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 01/05/2016 09:07 AM, Vitaly Burovoy wrote:

On 1/4/16, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

It seems we got majority approval on the design of this patch, and no
disagreement; the last submitted version appears to implement that.
There's no documentation change in the patch though. I'm marking it as
Waiting on Author; please resubmit with necessary doc changes.

Thank you!
Version 3 of the patch with touched documentation in the attachment.

I decided to mark it as a note, because that separation
(monotonic/oscillation fields) is not obvious and for most values the
function "extract" works as expected (e.g. does not give an error)
until special values are (casually?) passed.

I have reviewed this patch. It applies and compiles cleanly and
implements the behavior reached by consensus.

The documentation is a little light, but I don't see what else needs to
be said.

The code is clean and well commented. All extraction options are supported.

Regression tests are present and seemingly complete.

I looked around for other places where this code should be used and
didn't find any. I am marking this patch Ready for Committer.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#17

Tom Lane

tgl@sss.pgh.pa.us

almost 10 years ago

In reply to: Vik Fearing (#16)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

Vik Fearing <vik@2ndquadrant.fr> writes:

I looked around for other places where this code should be used and
didn't find any. I am marking this patch Ready for Committer.

I pushed this with some adjustments, mainly to make sure that the
erroneous-units errors exactly match those that would be thrown in
the main code line.

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

#18

Vitaly Burovoy

vitaly.burovoy@gmail.com

almost 10 years ago

In reply to: Tom Lane (#17)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 1/21/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Vik Fearing <vik@2ndquadrant.fr> writes:

I looked around for other places where this code should be used and
didn't find any. I am marking this patch Ready for Committer.

I pushed this with some adjustments, mainly to make sure that the
erroneous-units errors exactly match those that would be thrown in
the main code line.

regards, tom lane

Thank you! I didn't pay enough attention to it at that time.

--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#19

Vik Fearing

vik@2ndquadrant.fr

almost 10 years ago

In reply to: Tom Lane (#17)

Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

On 01/22/2016 04:28 AM, Tom Lane wrote:

Vik Fearing <vik@2ndquadrant.fr> writes:

I looked around for other places where this code should be used and
didn't find any. I am marking this patch Ready for Committer.

I pushed this with some adjustments, mainly to make sure that the
erroneous-units errors exactly match those that would be thrown in
the main code line.

Thanks!
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers