[PATCH] Supporting +-Infinity values by to_timestamp(float8)
Hello, Hackers!
I worked on a patch[1]http://git.postgresql.org/pg/commitdiff/647d87c56ab6da70adb753c08d7cdf7ee905ea8a allows "EXTRACT(epoch FROM
+-Inf::timestamp[tz])" to return "+-Inf::float8".
There is an opposite function "to_timestamp(float8)" which now defined as:
SELECT ('epoch'::timestamptz + $1 * '1 second'::interval)
Since intervals do not support infinity values, it is impossible to do
something like:
SELECT to_timestamp('infinity'::float8);
... which is not good.
Supporting of such converting is in the TODO list[2]https://wiki.postgresql.org/wiki/Todo#Dates_and_Times (by "converting
between infinity timestamp and float8").
Proposed patch implements it.
There is an other patch in the CF[3]https://commitfest.postgresql.org/9/540/ 2016-03 implements checking of
timestamp[tz] for being in allowed range. Since it is wise to set
(fix) the upper boundary of timestamp[tz]s, I've included the file
"src/include/datatype/timestamp.h" from there to check that an input
value and a result are in the allowed range.
There is no changes in a documentation because allowed range is the
same as officially supported[4]http://www.postgresql.org/docs/devel/static/datatype-datetime.html (i.e. until 294277 AD).
[1]: http://git.postgresql.org/pg/commitdiff/647d87c56ab6da70adb753c08d7cdf7ee905ea8a
[2]: https://wiki.postgresql.org/wiki/Todo#Dates_and_Times
[3]: https://commitfest.postgresql.org/9/540/
[4]: http://www.postgresql.org/docs/devel/static/datatype-datetime.html
--
Best regards,
Vitaly Burovoy
Attachments:
to_timestamp_infs.v001.patchapplication/octet-stream; name=to_timestamp_infs.v001.patchDownload
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 1525d2a..46140ef 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -475,6 +475,79 @@ timestamptz_in(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMPTZ(result);
}
+/* to_timestamp(double precision)
+ * Convert UNIX epoch to timestamptz.
+ */
+Datum
+unixtime_timestamptz(PG_FUNCTION_ARGS)
+{
+ float8 seconds = PG_GETARG_FLOAT8(0);
+ TimestampTz result;
+
+ int is_inf = is_infinite(seconds);
+
+ static const float8 epoch_lbound = (float8)SECS_PER_DAY * -UNIX_EPOCH_JDATE;
+ static const float8 epoch_ubound = (float8)SECS_PER_DAY *
+ (JULIAN_MAX4STAMPS - UNIX_EPOCH_JDATE);
+
+ if(isnan(seconds))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for UNIX epoch \"%f\"", seconds),
+ errhint("Valid units for this parameter are finite floats, \"Infinity\" and \"-Infinity\"")));
+
+ if (is_inf < 0)
+ TIMESTAMP_NOBEGIN(result);
+
+ else if (is_inf > 0)
+ TIMESTAMP_NOEND(result);
+
+ else
+ {
+ if (seconds < epoch_lbound)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("UNIX epoch out of range: \"%lf\"", seconds),
+ errhint("Minimal UNIX epoch value is \"%lf\"", epoch_lbound)));
+
+ if (seconds >= epoch_ubound)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("UNIX epoch out of range: \"%lf\"", seconds),
+ errhint("Maximal UNIX epoch value is \"%lf\"", epoch_ubound)));
+
+ /* Convert seconds to Postgres' epoch */
+ seconds -= ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
+
+#ifdef HAVE_INT64_TIMESTAMP
+ /*
+ * Here should be just "result = seconds * USECS_PER_SEC;",
+ * but for big values it leads loosing precision.
+ * Do as many operations as possible as integers.
+ */
+ {
+ int64 seconds_int = (int64)seconds;
+ fsec_t msec = (seconds - seconds_int) * USECS_PER_SEC;
+
+ result = (seconds_int * USECS_PER_SEC) + msec;
+ }
+#else
+ result = seconds;
+#endif
+ /*
+ * There can't be overflow. Just check timestamp for correctness again
+ * because of possible loosing precision on float8-int86 conversion.
+ */
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("UNIX epoch out of range: \"%lf\"",
+ PG_GETARG_FLOAT8(0))));
+ }
+
+ PG_RETURN_TIMESTAMP(result);
+}
+
/*
* Try to parse a timezone specification, and return its timezone offset value
* if it's acceptable. Otherwise, an error is thrown.
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 62b9125..7f3433a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1202,7 +1202,7 @@ DATA(insert OID = 1154 ( timestamptz_lt PGNSP PGUID 12 1 0 0 0 f f f t t f i
DATA(insert OID = 1155 ( timestamptz_le PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "1184 1184" _null_ _null_ _null_ _null_ _null_ timestamp_le _null_ _null_ _null_ ));
DATA(insert OID = 1156 ( timestamptz_ge PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "1184 1184" _null_ _null_ _null_ _null_ _null_ timestamp_ge _null_ _null_ _null_ ));
DATA(insert OID = 1157 ( timestamptz_gt PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "1184 1184" _null_ _null_ _null_ _null_ _null_ timestamp_gt _null_ _null_ _null_ ));
-DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 14 1 0 0 0 f f f f t f i s 1 0 1184 "701" _null_ _null_ _null_ _null_ _null_ "select (''epoch''::pg_catalog.timestamptz + $1 * ''1 second''::pg_catalog.interval)" _null_ _null_ _null_ ));
+DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 1184 "701" _null_ _null_ _null_ _null_ _null_ unixtime_timestamptz _null_ _null_ _null_ ));
DESCR("convert UNIX epoch to timestamptz");
DATA(insert OID = 3995 ( timestamp_zone_transform PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2281 "2281" _null_ _null_ _null_ _null_ _null_ timestamp_zone_transform _null_ _null_ _null_ ));
DESCR("transform a time zone adjustment");
diff --git a/src/include/datatype/timestamp.h b/src/include/datatype/timestamp.h
index 12a2a8a..9b21ba8 100644
--- a/src/include/datatype/timestamp.h
+++ b/src/include/datatype/timestamp.h
@@ -105,6 +105,20 @@ typedef struct
#define USECS_PER_MINUTE INT64CONST(60000000)
#define USECS_PER_SEC INT64CONST(1000000)
+#ifdef HAVE_INT64_TIMESTAMP
+#define MIN_TIMESTAMP INT64CONST(-211813488000000000)
+/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */
+#define MAX_TIMESTAMP INT64CONST(9223371331200000000)
+/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 * USECS_PER_SEC */
+#else
+#define MIN_TIMESTAMP -211813488000.0
+/* == (0 - POSTGRES_EPOCH_JDATE) * 86400 */
+#define MAX_TIMESTAMP 9223371331200.0
+/* == (JULIAN_MAX4STAMPS - POSTGRES_EPOCH_JDATE) * 86400 */
+#endif
+
+#define IS_VALID_TIMESTAMP(j) ((MIN_TIMESTAMP <= (j)) && ((j) < MAX_TIMESTAMP))
+
/*
* We allow numeric timezone offsets up to 15:59:59 either way from Greenwich.
* Currently, the record holders for wackiest offsets in actual use are zones
@@ -156,6 +170,7 @@ typedef struct
#define JULIAN_MINMONTH (11)
#define JULIAN_MINDAY (24)
#define JULIAN_MAXYEAR (5874898)
+#define JULIAN_MAXYEAR4STAMPS (294277)
#define IS_VALID_JULIAN(y,m,d) \
(((y) > JULIAN_MINYEAR \
@@ -164,10 +179,22 @@ typedef struct
|| ((m) == JULIAN_MINMONTH && (d) >= JULIAN_MINDAY)))) \
&& (y) < JULIAN_MAXYEAR)
+#define IS_VALID_JULIAN4STAMPS(y,m,d) \
+ (((y) > JULIAN_MINYEAR \
+ || ((y) == JULIAN_MINYEAR && \
+ ((m) > JULIAN_MINMONTH \
+ || ((m) == JULIAN_MINMONTH && (d) >= JULIAN_MINDAY)))) \
+ && (y) < JULIAN_MAXYEAR4STAMPS)
+
#define JULIAN_MAX (2147483494) /* == date2j(JULIAN_MAXYEAR, 1, 1) */
+#define JULIAN_MAX4STAMPS (109203528) /* == date2j(JULIAN_MAXYEAR4STAMPS, 1, 1) */
/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
+#define IS_VALID_DATE(j) \
+ ((-POSTGRES_EPOCH_JDATE <= (j)) && \
+ ((j) < (JULIAN_MAX - POSTGRES_EPOCH_JDATE)))
+
#endif /* DATATYPE_TIMESTAMP_H */
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index fbead3a..e18211d 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -124,6 +124,7 @@ extern Datum timestamp_cmp_timestamptz(PG_FUNCTION_ARGS);
extern Datum make_timestamp(PG_FUNCTION_ARGS);
extern Datum make_timestamptz(PG_FUNCTION_ARGS);
extern Datum make_timestamptz_at_timezone(PG_FUNCTION_ARGS);
+extern Datum unixtime_timestamptz(PG_FUNCTION_ARGS);
extern Datum timestamptz_eq_timestamp(PG_FUNCTION_ARGS);
extern Datum timestamptz_ne_timestamp(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 40a2254..8d7f981 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2232,6 +2232,66 @@ SELECT make_timestamptz(2007, 12, 9, 3, 0, 0, 'VET');
Sun Dec 09 07:30:00 2007 UTC
(1 row)
+SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00
+ to_timestamp
+------------------------------
+ Thu Jan 01 00:00:00 1970 UTC
+(1 row)
+
+SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00
+ to_timestamp
+------------------------------
+ Sat Jan 01 00:00:00 2000 UTC
+(1 row)
+
+SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00
+ to_timestamp
+-------------------------------------
+ Fri Jan 01 12:34:56.789012 2010 UTC
+(1 row)
+
+-- corner cases
+SELECT to_timestamp(-1e20::float8); -- Error: UNIX epoch out of range
+ERROR: UNIX epoch out of range: "-100000000000000000000.000000"
+HINT: Minimal UNIX epoch value is "-210866803200.000000"
+SELECT to_timestamp(-210866803200.0625); -- Error: UNIX epoch out of range
+ERROR: UNIX epoch out of range: "-210866803200.062500"
+HINT: Minimal UNIX epoch value is "-210866803200.000000"
+SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC
+ to_timestamp
+---------------------------------
+ Mon Nov 24 00:00:00 4714 UTC BC
+(1 row)
+
+-- Maximal allowed input value
+SELECT to_timestamp(9224318015999.875); -- 294276-12-31 23:59:59.875+00
+ to_timestamp
+------------------------------------
+ Sun Dec 31 23:59:59.875 294276 UTC
+(1 row)
+
+SELECT to_timestamp(9224318016000); -- Error: UNIX epoch out of range
+ERROR: UNIX epoch out of range: "9224318016000.000000"
+HINT: Maximal UNIX epoch value is "9224318016000.000000"
+SELECT to_timestamp(1e20::float8); -- Error: UNIX epoch out of range
+ERROR: UNIX epoch out of range: "100000000000000000000.000000"
+HINT: Maximal UNIX epoch value is "9224318016000.000000"
+-- infinity values
+SELECT to_timestamp(' Infinity'::float);
+ to_timestamp
+--------------
+ infinity
+(1 row)
+
+SELECT to_timestamp('-Infinity'::float);
+ to_timestamp
+--------------
+ -infinity
+(1 row)
+
+SELECT to_timestamp('NaN'::float);
+ERROR: invalid value for UNIX epoch "nan"
+HINT: Valid units for this parameter are finite floats, "Infinity" and "-Infinity"
SET TimeZone to 'Europe/Moscow';
SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
timestamptz
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index f4b455e..fdad178 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -379,6 +379,23 @@ SELECT '2007-12-09 04:00:00'::timestamp AT TIME ZONE 'VET';
SELECT make_timestamptz(2007, 12, 9, 2, 0, 0, 'VET');
SELECT make_timestamptz(2007, 12, 9, 3, 0, 0, 'VET');
+SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00
+SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00
+SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00
+-- corner cases
+SELECT to_timestamp(-1e20::float8); -- Error: UNIX epoch out of range
+SELECT to_timestamp(-210866803200.0625); -- Error: UNIX epoch out of range
+SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC
+-- Maximal allowed input value
+SELECT to_timestamp(9224318015999.875); -- 294276-12-31 23:59:59.875+00
+SELECT to_timestamp(9224318016000); -- Error: UNIX epoch out of range
+SELECT to_timestamp(1e20::float8); -- Error: UNIX epoch out of range
+-- infinity values
+SELECT to_timestamp(' Infinity'::float);
+SELECT to_timestamp('-Infinity'::float);
+SELECT to_timestamp('NaN'::float);
+
+
SET TimeZone to 'Europe/Moscow';
SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
Added to the CF 2016-03:
https://commitfest.postgresql.org/9/546/
--
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
On 2/26/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
Proposed patch implements it.
I'm sorry, I forgot to leave a note for reviewers and committers:
This patch requires CATALOG_VERSION_NO be bumped.
Since pg_proc.h entry has changed, it is important to check and run
regress tests on a new cluster (as if CATALOG_VERSION_NO was bumped).
--
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
27.02.2016 09:57, Vitaly Burovoy:
Hello, Hackers!
I worked on a patch[1] allows "EXTRACT(epoch FROM
+-Inf::timestamp[tz])" to return "+-Inf::float8".
There is an opposite function "to_timestamp(float8)" which now defined as:
SELECT ('epoch'::timestamptz + $1 * '1 second'::interval)
Hi,
thank you for the patches.
Could you explain, whether they depend on each other?
Since intervals do not support infinity values, it is impossible to do
something like:SELECT to_timestamp('infinity'::float8);
... which is not good.
Supporting of such converting is in the TODO list[2] (by "converting
between infinity timestamp and float8").
You mention intervals here, and TODO item definitely says about
'infinity' interval,
while patch and all the following discussion concerns to timestamps.
Is it a typo or I misunderstood something important?
I assumed that following query will work, but it isn't. Could you
clarify that?
select to_timestamp('infinity'::interval);
Proposed patch implements it.
There is an other patch in the CF[3] 2016-03 implements checking of
timestamp[tz] for being in allowed range. Since it is wise to set
(fix) the upper boundary of timestamp[tz]s, I've included the file
"src/include/datatype/timestamp.h" from there to check that an input
value and a result are in the allowed range.There is no changes in a documentation because allowed range is the
same as officially supported[4] (i.e. until 294277 AD).
I think that you should update documentation. At least description of
epoch on this page:
http://www.postgresql.org/docs/devel/static/functions-datetime.html
Here is how you can convert an epoch value back to a time stamp:
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';
(The |to_timestamp| function encapsulates the above conversion.)
More thoughts about the patch:
1. When I copy value from hints for min and max values (see examples
below), it works fine for min, while max still leads to error.
It comes from the check "if (seconds >= epoch_ubound)". I wonder,
whether you should change hint message?
select to_timestamp(-210866803200.000000);
to_timestamp
---------------------------------
4714-11-24 02:30:17+02:30:17 BC
(1 row)
select to_timestamp(9224318016000.000000);
ERROR: UNIX epoch out of range: "9224318016000.000000"
HINT: Maximal UNIX epoch value is "9224318016000.000000"
2. There is a comment about JULIAN_MAXYEAR inaccuracy in timestamp.h:
* IS_VALID_JULIAN checks the minimum date exactly, but is a bit sloppy
* about the maximum, since it's far enough out to not be especially
* interesting.
Maybe you can expand it?
- Is JULIAN_MAXYEAR4STAMPS helps to avoid overflow in all possible cases?
- Why do we need to hold both definitions? I suppose, it's a matter of
backward compatibility, isn't it?
3. (nitpicking) I don't sure about "4STAMPS" suffix. "4" is nice
abbreviation, but it seems slightly confusing to me.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
On 3/4/16, Anastasia Lubennikova <a.lubennikova@postgrespro.ru> wrote:
27.02.2016 09:57, Vitaly Burovoy:
Hello, Hackers!
I worked on a patch[1] allows "EXTRACT(epoch FROM
+-Inf::timestamp[tz])" to return "+-Inf::float8".
There is an opposite function "to_timestamp(float8)" which now defined
as:
SELECT ('epoch'::timestamptz + $1 * '1 second'::interval)Hi,
thank you for the patches.
Thank you for the review.
Could you explain, whether they depend on each other?
Only logically. They reverse each other:
postgres=# SELECT v, to_timestamp(v), extract(epoch FROM to_timestamp(v)) FROM
postgres-# unnest(ARRAY['+inf', '-inf', 0, 65536, 982384720.12]::float8[]) v;
v | to_timestamp | date_part
--------------+---------------------------+--------------
Infinity | infinity | Infinity
-Infinity | -infinity | -Infinity
0 | 1970-01-01 00:00:00+00 | 0
65536 | 1970-01-01 18:12:16+00 | 65536
982384720.12 | 2001-02-17 04:38:40.12+00 | 982384720.12
(5 rows)
Since intervals do not support infinity values, it is impossible to do
something like:SELECT to_timestamp('infinity'::float8);
... which is not good.
Supporting of such converting is in the TODO list[2] (by "converting
between infinity timestamp and float8").You mention intervals here, and TODO item definitely says about
'infinity' interval,
Yes, it is in the same block. But I wanted to point to the link
"converting between infinity timestamp and float8". There are two-way
conversion examples.
while patch and all the following discussion concerns to timestamps.
Is it a typo or I misunderstood something important?
It is just a reason why I rewrote it as an internal function.
I asked whether to just rewrite the function
"pg_catalog.to_timestamp(float8)" as an internal one or to add support
of infinite intervals. Tom Lane answered[5]/messages/by-id/21367.1447046745@sss.pgh.pa.us -- Best regards, Vitaly Burovoy "you should stay away from
infinite intervals".
So I left intervals as is.
I assumed that following query will work, but it isn't. Could you
clarify that?
select to_timestamp('infinity'::interval);
It is not hard. There is no logical way to convert interval (e.g.
"5minutes") to a timestamp (or date).
There never was a function "to_timestamp(interval)" and never will be.
postgres=# select to_timestamp('5min'::interval);
ERROR: function to_timestamp(interval) does not exist
LINE 1: select to_timestamp('1min'::interval);
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
Proposed patch implements it.
There is an other patch in the CF[3] 2016-03 implements checking of
timestamp[tz] for being in allowed range. Since it is wise to set
(fix) the upper boundary of timestamp[tz]s, I've included the file
"src/include/datatype/timestamp.h" from there to check that an input
value and a result are in the allowed range.There is no changes in a documentation because allowed range is the
same as officially supported[4] (i.e. until 294277 AD).I think that you should update documentation. At least description of
epoch on this page:
http://www.postgresql.org/docs/devel/static/functions-datetime.html
Thank you very much for pointing where it is located (I saw only
"to_timestamp(TEXT, TEXT)").
I'll think how to update it.
More thoughts about the patch:
1. When I copy value from hints for min and max values (see examples
below), it works fine for min, while max still leads to error.
It comes from the check "if (seconds >= epoch_ubound)". I wonder,
whether you should change hint message?select to_timestamp(-210866803200.000000);
to_timestamp
---------------------------------
4714-11-24 02:30:17+02:30:17 BC
(1 row)select to_timestamp(9224318016000.000000);
ERROR: UNIX epoch out of range: "9224318016000.000000"
HINT: Maximal UNIX epoch value is "9224318016000.000000"
I agree, it is a little confusing. Do you (or anyone) know how to
construct a condensed phrase that it is an exclusive upper bound of an
allowed UNIX epoch range?
2. There is a comment about JULIAN_MAXYEAR inaccuracy in timestamp.h:
* IS_VALID_JULIAN checks the minimum date exactly, but is a bit sloppy
* about the maximum, since it's far enough out to not be especially
* interesting.
It is just about the accuracy to the day for a lower bound, and to the
year (not to a day) for an upper bound.
Maybe you can expand it?
- Is JULIAN_MAXYEAR4STAMPS helps to avoid overflow in all possible cases?
- Why do we need to hold both definitions? I suppose, it's a matter of
backward compatibility, isn't it?
Yes. I tried to be less invasive from the point of view of endusers.
They can be sure if they follow the documentation they won't get into
trouble.
3. (nitpicking) I don't sure about "4STAMPS" suffix. "4" is nice
abbreviation, but it seems slightly confusing to me.
It doesn't matter for me what it is called, it is short enough and
reflects a type on which it is applied.
What would the best name be for it?
[5]: /messages/by-id/21367.1447046745@sss.pgh.pa.us -- Best regards, Vitaly Burovoy
--
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
On 3/4/16 2:56 PM, Vitaly Burovoy wrote:
On 3/4/16, Anastasia Lubennikova <a.lubennikova@postgrespro.ru> wrote:
I think that you should update documentation. At least description of
epoch on this page:
http://www.postgresql.org/docs/devel/static/functions-datetime.htmlThank you very much for pointing where it is located (I saw only
"to_timestamp(TEXT, TEXT)").
I'll think how to update it.
Vitaly, have you decided how to update this yet?
3. (nitpicking) I don't sure about "4STAMPS" suffix. "4" is nice
abbreviation, but it seems slightly confusing to me.It doesn't matter for me what it is called, it is short enough and
reflects a type on which it is applied.
What would the best name be for it?
Anastasia, any suggestions for a better name, or just leave it as is?
I'm not in favor of the "4", either. I think I would prefer
JULIAN_MAXYEAR_STAMP.
--
-David
david@pgmasters.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
15.03.2016 22:28, David Steele:
On 3/4/16 2:56 PM, Vitaly Burovoy wrote:
On 3/4/16, Anastasia Lubennikova <a.lubennikova@postgrespro.ru> wrote:
I think that you should update documentation. At least description of
epoch on this page:
http://www.postgresql.org/docs/devel/static/functions-datetime.htmlThank you very much for pointing where it is located (I saw only
"to_timestamp(TEXT, TEXT)").
I'll think how to update it.Vitaly, have you decided how to update this yet?
3. (nitpicking) I don't sure about "4STAMPS" suffix. "4" is nice
abbreviation, but it seems slightly confusing to me.It doesn't matter for me what it is called, it is short enough and
reflects a type on which it is applied.
What would the best name be for it?Anastasia, any suggestions for a better name, or just leave it as is?
I'm not in favor of the "4", either. I think I would prefer
JULIAN_MAXYEAR_STAMP.
This point is related to another patch
https://commitfest.postgresql.org/9/540/.
And added to this patch just for compatibility.
If Tom wouldn't change the name of the macros there, I don't see any
reasons why should we do it in this patch.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2016-03-15, David Steele <david@pgmasters.net> wrote:
On 3/4/16 2:56 PM, Vitaly Burovoy wrote:
On 3/4/16, Anastasia Lubennikova <a.lubennikova@postgrespro.ru> wrote:
I think that you should update documentation. At least description of
epoch on this page:
http://www.postgresql.org/docs/devel/static/functions-datetime.htmlThank you very much for pointing where it is located (I saw only
"to_timestamp(TEXT, TEXT)").
I'll think how to update it.Vitaly, have you decided how to update this yet?
Yes, there are three versions:
* remove mentioning how to get timestamptz from UNIX stamp;
* leave a note how to get timestamptz and add a note that such
encapsulation existed prior to 9.6;
* replace to the proposed current behavior (without interval).
I decided to implement the third case (but a result there has a time
zone which can be different, so the result can be not exactly same for
a concrete user). If a committer decides to do somehow else, he is
free to choose one from the list above or to do something else.
3. (nitpicking) I don't sure about "4STAMPS" suffix. "4" is nice
abbreviation, but it seems slightly confusing to me.It doesn't matter for me what it is called, it is short enough and
reflects a type on which it is applied.
What would the best name be for it?Anastasia, any suggestions for a better name, or just leave it as is?
I'm not in favor of the "4", either. I think I would prefer
JULIAN_MAXYEAR_STAMP.
It turns out that Tom has changed almost one third of timestamp.h and
now the constant has a name "TIMESTAMP_END_JULIAN".
I've rebased the patch to the current master (5db5146) and changed it
according to the new timestamp.h.
Now it passes both versions (integer and float timestamps).
I deleted test for the upper boundary for integer timestamps, changed
a little comments.
I decided to delete hints about minimum and maximum allowed values
because no one type has such hint.
Please find attached a new version of the patch.
--
Best regards,
Vitaly Burovoy
Attachments:
to_timestamp_infs.v002.patchapplication/octet-stream; name=to_timestamp_infs.v002.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 000489d..47219bb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -7358,12 +7358,9 @@ SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
stamp:
</para>
<screen>
-SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';
+SELECT to_timestamp(982384720.12);
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-17 04:38:40.12+00</computeroutput>
</screen>
- <para>
- (The <function>to_timestamp</> function encapsulates the above
- conversion.)
- </para>
</listitem>
</varlistentry>
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 3f013e3..9dad256 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -476,6 +476,74 @@ timestamptz_in(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMPTZ(result);
}
+/* to_timestamp(double precision)
+ * Convert UNIX epoch to timestamptz.
+ */
+Datum
+unixtime_timestamptz(PG_FUNCTION_ARGS)
+{
+ float8 seconds = PG_GETARG_FLOAT8(0);
+ TimestampTz result;
+
+ int is_inf = is_infinite(seconds);
+
+ static const float8 epoch_lbound = (float8)SECS_PER_DAY * -UNIX_EPOCH_JDATE;
+ static const float8 epoch_ubound = (float8)SECS_PER_DAY *
+ (TIMESTAMP_END_JULIAN - UNIX_EPOCH_JDATE);
+
+ if(isnan(seconds))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for UNIX epoch \"%f\"", seconds),
+ errhint("Valid units for this parameter are finite floats, \"Infinity\" and \"-Infinity\"")));
+
+ if (is_inf < 0)
+ TIMESTAMP_NOBEGIN(result);
+
+ else if (is_inf > 0)
+ TIMESTAMP_NOEND(result);
+
+ else
+ {
+ if (seconds < epoch_lbound)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("UNIX epoch out of range: \"%lf\"", seconds)));
+
+ if (seconds >= epoch_ubound)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("UNIX epoch out of range: \"%lf\"", seconds)));
+
+ /* Convert UNIX epoch to Postgres epoch */
+ seconds -= ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY);
+
+#ifdef HAVE_INT64_TIMESTAMP
+ /*
+ * Here should be just "result = seconds * USECS_PER_SEC;",
+ * but for big values it leads loosing precision.
+ * Do as many operations as possible as integers.
+ */
+ {
+ int64 seconds_int = (int64)seconds;
+ fsec_t msec = (seconds - seconds_int) * USECS_PER_SEC;
+
+ result = (seconds_int * USECS_PER_SEC) + msec;
+ }
+#else
+ result = seconds;
+#endif
+ /* final range check catches just-out-of-range timestamps */
+ if (!IS_VALID_TIMESTAMP(result))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("UNIX epoch out of range: \"%lf\"",
+ PG_GETARG_FLOAT8(0))));
+ }
+
+ PG_RETURN_TIMESTAMP(result);
+}
+
/*
* Try to parse a timezone specification, and return its timezone offset value
* if it's acceptable. Otherwise, an error is thrown.
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index ceb8129..d7b94e8 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1202,7 +1202,7 @@ DATA(insert OID = 1154 ( timestamptz_lt PGNSP PGUID 12 1 0 0 0 f f f t t f i
DATA(insert OID = 1155 ( timestamptz_le PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "1184 1184" _null_ _null_ _null_ _null_ _null_ timestamp_le _null_ _null_ _null_ ));
DATA(insert OID = 1156 ( timestamptz_ge PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "1184 1184" _null_ _null_ _null_ _null_ _null_ timestamp_ge _null_ _null_ _null_ ));
DATA(insert OID = 1157 ( timestamptz_gt PGNSP PGUID 12 1 0 0 0 f f f t t f i s 2 0 16 "1184 1184" _null_ _null_ _null_ _null_ _null_ timestamp_gt _null_ _null_ _null_ ));
-DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 14 1 0 0 0 f f f f t f i s 1 0 1184 "701" _null_ _null_ _null_ _null_ _null_ "select (''epoch''::pg_catalog.timestamptz + $1 * ''1 second''::pg_catalog.interval)" _null_ _null_ _null_ ));
+DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 1184 "701" _null_ _null_ _null_ _null_ _null_ unixtime_timestamptz _null_ _null_ _null_ ));
DESCR("convert UNIX epoch to timestamptz");
DATA(insert OID = 3995 ( timestamp_zone_transform PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2281 "2281" _null_ _null_ _null_ _null_ _null_ timestamp_zone_transform _null_ _null_ _null_ ));
DESCR("transform a time zone adjustment");
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index fbead3a..e18211d 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -124,6 +124,7 @@ extern Datum timestamp_cmp_timestamptz(PG_FUNCTION_ARGS);
extern Datum make_timestamp(PG_FUNCTION_ARGS);
extern Datum make_timestamptz(PG_FUNCTION_ARGS);
extern Datum make_timestamptz_at_timezone(PG_FUNCTION_ARGS);
+extern Datum unixtime_timestamptz(PG_FUNCTION_ARGS);
extern Datum timestamptz_eq_timestamp(PG_FUNCTION_ARGS);
extern Datum timestamptz_ne_timestamp(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index fffcaf4..e3aab6a 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2256,6 +2256,54 @@ SELECT make_timestamptz(2007, 12, 9, 3, 0, 0, 'VET');
Sun Dec 09 07:30:00 2007 UTC
(1 row)
+SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00
+ to_timestamp
+------------------------------
+ Thu Jan 01 00:00:00 1970 UTC
+(1 row)
+
+SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00
+ to_timestamp
+------------------------------
+ Sat Jan 01 00:00:00 2000 UTC
+(1 row)
+
+SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00
+ to_timestamp
+-------------------------------------
+ Fri Jan 01 12:34:56.789012 2010 UTC
+(1 row)
+
+-- edge cases
+SELECT to_timestamp(-1e20::float8); -- Error: UNIX epoch out of range
+ERROR: UNIX epoch out of range: "-100000000000000000000.000000"
+SELECT to_timestamp(-210866803200.0625); -- Error: UNIX epoch out of range
+ERROR: UNIX epoch out of range: "-210866803200.062500"
+SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC
+ to_timestamp
+---------------------------------
+ Mon Nov 24 00:00:00 4714 UTC BC
+(1 row)
+
+-- The upper boundary differs between integer and float timestamps, so check the biggest one
+SELECT to_timestamp(185331707078400::float8); -- Error: UNIX epoch out of range
+ERROR: UNIX epoch out of range: "185331707078400.000000"
+-- nonfinite values
+SELECT to_timestamp(' Infinity'::float);
+ to_timestamp
+--------------
+ infinity
+(1 row)
+
+SELECT to_timestamp('-Infinity'::float);
+ to_timestamp
+--------------
+ -infinity
+(1 row)
+
+SELECT to_timestamp('NaN'::float);
+ERROR: invalid value for UNIX epoch "nan"
+HINT: Valid units for this parameter are finite floats, "Infinity" and "-Infinity"
SET TimeZone to 'Europe/Moscow';
SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
timestamptz
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 03dbc05..71c1657 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -386,6 +386,21 @@ SELECT '2007-12-09 04:00:00'::timestamp AT TIME ZONE 'VET';
SELECT make_timestamptz(2007, 12, 9, 2, 0, 0, 'VET');
SELECT make_timestamptz(2007, 12, 9, 3, 0, 0, 'VET');
+SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00
+SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00
+SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00
+-- edge cases
+SELECT to_timestamp(-1e20::float8); -- Error: UNIX epoch out of range
+SELECT to_timestamp(-210866803200.0625); -- Error: UNIX epoch out of range
+SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC
+-- The upper boundary differs between integer and float timestamps, so check the biggest one
+SELECT to_timestamp(185331707078400::float8); -- Error: UNIX epoch out of range
+-- nonfinite values
+SELECT to_timestamp(' Infinity'::float);
+SELECT to_timestamp('-Infinity'::float);
+SELECT to_timestamp('NaN'::float);
+
+
SET TimeZone to 'Europe/Moscow';
SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
Anastasia Lubennikova <a.lubennikova@postgrespro.ru> writes:
15.03.2016 22:28, David Steele:
I'm not in favor of the "4", either. I think I would prefer
JULIAN_MAXYEAR_STAMP.
This point is related to another patch
https://commitfest.postgresql.org/9/540/.
And added to this patch just for compatibility.
If Tom wouldn't change the name of the macros there, I don't see any
reasons why should we do it in this patch.
Yeah, I didn't like the "4STAMPS" terminology at all. It ended up being
moot for that patch, because the answer eventually turned out to be that
we needed to decouple the Julian-date boundaries from the datatype
boundaries altogether. But I would've renamed those macros to something
else if they'd stayed.
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
17.03.2016 06:27, Vitaly Burovoy:
On 2016-03-15, David Steele <david@pgmasters.net> wrote:
On 3/4/16 2:56 PM, Vitaly Burovoy wrote:
On 3/4/16, Anastasia Lubennikova <a.lubennikova@postgrespro.ru> wrote:
I think that you should update documentation. At least description of
epoch on this page:
http://www.postgresql.org/docs/devel/static/functions-datetime.htmlThank you very much for pointing where it is located (I saw only
"to_timestamp(TEXT, TEXT)").
I'll think how to update it.Vitaly, have you decided how to update this yet?
Yes, there are three versions:
* remove mentioning how to get timestamptz from UNIX stamp;
* leave a note how to get timestamptz and add a note that such
encapsulation existed prior to 9.6;
* replace to the proposed current behavior (without interval).I decided to implement the third case (but a result there has a time
zone which can be different, so the result can be not exactly same for
a concrete user). If a committer decides to do somehow else, he is
free to choose one from the list above or to do something else.3. (nitpicking) I don't sure about "4STAMPS" suffix. "4" is nice
abbreviation, but it seems slightly confusing to me.It doesn't matter for me what it is called, it is short enough and
reflects a type on which it is applied.
What would the best name be for it?Anastasia, any suggestions for a better name, or just leave it as is?
I'm not in favor of the "4", either. I think I would prefer
JULIAN_MAXYEAR_STAMP.It turns out that Tom has changed almost one third of timestamp.h and
now the constant has a name "TIMESTAMP_END_JULIAN".I've rebased the patch to the current master (5db5146) and changed it
according to the new timestamp.h.Now it passes both versions (integer and float timestamps).
I deleted test for the upper boundary for integer timestamps, changed
a little comments.I decided to delete hints about minimum and maximum allowed values
because no one type has such hint.Please find attached a new version of the patch.
I think, I should write something as a reviewer.
I read the patch again and I don't see any issues with it.
It applies to the master and works as expected. So I'll mark it as
"Ready for committer"
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Anastasia Lubennikova <a.lubennikova@postgrespro.ru> writes:
17.03.2016 06:27, Vitaly Burovoy:
Please find attached a new version of the patch.
I think, I should write something as a reviewer.
I read the patch again and I don't see any issues with it.
It applies to the master and works as expected. So I'll mark it as
"Ready for committer"
Pushed with minor adjustments.
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
On 3/29/16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pushed with minor adjustments.
regards, tom lane
Thank you very much!
--
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