Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite511d87

Browse files
committed
Allow to_timestamp(float8) to convert float infinity to timestamp infinity.
With the original SQL-function implementation, such cases failed becausewe don't support infinite intervals. Converting the function to C letsus bypass the interval representation, which should be a bit faster aswell as more flexible.Vitaly Burovoy, reviewed by Anastasia Lubennikova
1 parent96f8373 commite511d87

File tree

7 files changed

+149
-26
lines changed

7 files changed

+149
-26
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 26 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -5579,15 +5579,6 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
55795579
argument is the value to be formatted and the second argument is a
55805580
template that defines the output or input format.
55815581
</para>
5582-
<para>
5583-
A single-argument <function>to_timestamp</function> function is also
5584-
available; it accepts a
5585-
<type>double precision</type> argument and converts from Unix epoch
5586-
(seconds since 1970-01-01 00:00:00+00) to
5587-
<type>timestamp with time zone</type>.
5588-
(<type>Integer</type> Unix epochs are implicitly cast to
5589-
<type>double precision</type>.)
5590-
</para>
55915582

55925583
<table id="functions-formatting-table">
55935584
<title>Formatting Functions</title>
@@ -5670,16 +5661,17 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
56705661
<entry>convert string to time stamp</entry>
56715662
<entry><literal>to_timestamp('05&nbsp;Dec&nbsp;2000', 'DD&nbsp;Mon&nbsp;YYYY')</literal></entry>
56725663
</row>
5673-
<row>
5674-
<entry><literal><function>to_timestamp(<type>double precision</type>)</function></literal></entry>
5675-
<entry><type>timestamp with time zone</type></entry>
5676-
<entry>convert Unix epoch to time stamp</entry>
5677-
<entry><literal>to_timestamp(1284352323)</literal></entry>
5678-
</row>
56795664
</tbody>
56805665
</tgroup>
56815666
</table>
56825667

5668+
<note>
5669+
<para>
5670+
There is also a single-argument <function>to_timestamp</function>
5671+
function; see <xref linkend="functions-datetime-table">.
5672+
</para>
5673+
</note>
5674+
56835675
<para>
56845676
In a <function>to_char</> output template string, there are certain
56855677
patterns that are recognized and replaced with appropriately-formatted
@@ -7060,8 +7052,8 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
70607052
<entry><type>timestamp with time zone</type></entry>
70617053
<entry>
70627054
Create timestamp with time zone from year, month, day, hour, minute
7063-
and seconds fields. When <parameter>timezone</parameter> is not specified,
7064-
thencurrent time zone is used.
7055+
and seconds fields; if <parameter>timezone</parameter> is not
7056+
specified, thecurrent time zone is used
70657057
</entry>
70667058
<entry><literal>make_timestamptz(2013, 7, 15, 8, 15, 23.5)</literal></entry>
70677059
<entry><literal>2013-07-15 08:15:23.5+01</literal></entry>
@@ -7127,6 +7119,19 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
71277119
<entry></entry>
71287120
<entry></entry>
71297121
</row>
7122+
<row>
7123+
<entry>
7124+
<indexterm>
7125+
<primary>to_timestamp</primary>
7126+
</indexterm>
7127+
<literal><function>to_timestamp(<type>double precision</type>)</function></literal>
7128+
</entry>
7129+
<entry><type>timestamp with time zone</type></entry>
7130+
<entry>Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to
7131+
timestamp</entry>
7132+
<entry><literal>to_timestamp(1284352323)</literal></entry>
7133+
<entry><literal>2010-09-13 04:32:03+00</literal></entry>
7134+
</row>
71307135
</tbody>
71317136
</tgroup>
71327137
</table>
@@ -7377,16 +7382,13 @@ SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
73777382
</screen>
73787383

73797384
<para>
7380-
Here is how youcan convert an epoch value back to a time
7381-
stamp:
7385+
Youcan convert an epoch value back to a time stamp
7386+
with <function>to_timestamp</>:
73827387
</para>
73837388
<screen>
7384-
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';
7389+
SELECT to_timestamp(982384720.12);
7390+
<lineannotation>Result: </lineannotation><computeroutput>2001-02-17 04:38:40.12+00</computeroutput>
73857391
</screen>
7386-
<para>
7387-
(The <function>to_timestamp</> function encapsulates the above
7388-
conversion.)
7389-
</para>
73907392
</listitem>
73917393
</varlistentry>
73927394

‎src/backend/utils/adt/timestamp.c

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -737,6 +737,64 @@ make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
737737
PG_RETURN_TIMESTAMPTZ(result);
738738
}
739739

740+
/*
741+
* to_timestamp(double precision)
742+
* Convert UNIX epoch to timestamptz.
743+
*/
744+
Datum
745+
float8_timestamptz(PG_FUNCTION_ARGS)
746+
{
747+
float8seconds=PG_GETARG_FLOAT8(0);
748+
TimestampTzresult;
749+
750+
/* Deal with NaN and infinite inputs ... */
751+
if (isnan(seconds))
752+
ereport(ERROR,
753+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
754+
errmsg("timestamp cannot be NaN")));
755+
756+
if (isinf(seconds))
757+
{
758+
if (seconds<0)
759+
TIMESTAMP_NOBEGIN(result);
760+
else
761+
TIMESTAMP_NOEND(result);
762+
}
763+
else
764+
{
765+
/* Out of range? */
766+
if (seconds<
767+
(float8)SECS_PER_DAY* (DATETIME_MIN_JULIAN-UNIX_EPOCH_JDATE))
768+
ereport(ERROR,
769+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
770+
errmsg("timestamp out of range: \"%g\"",seconds)));
771+
772+
if (seconds >=
773+
(float8)SECS_PER_DAY* (TIMESTAMP_END_JULIAN-UNIX_EPOCH_JDATE))
774+
ereport(ERROR,
775+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
776+
errmsg("timestamp out of range: \"%g\"",seconds)));
777+
778+
/* Convert UNIX epoch to Postgres epoch */
779+
seconds-= ((POSTGRES_EPOCH_JDATE-UNIX_EPOCH_JDATE)*SECS_PER_DAY);
780+
781+
#ifdefHAVE_INT64_TIMESTAMP
782+
result=seconds*USECS_PER_SEC;
783+
#else
784+
result=seconds;
785+
#endif
786+
787+
/* Recheck in case roundoff produces something just out of range */
788+
if (!IS_VALID_TIMESTAMP(result))
789+
ereport(ERROR,
790+
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
791+
errmsg("timestamp out of range: \"%g\"",
792+
PG_GETARG_FLOAT8(0))));
793+
}
794+
795+
PG_RETURN_TIMESTAMP(result);
796+
}
797+
740798
/* timestamptz_out()
741799
* Convert a timestamp to external form.
742800
*/

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201603291
56+
#defineCATALOG_VERSION_NO201603292
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1202,7 +1202,7 @@ DATA(insert OID = 1154 ( timestamptz_lt PGNSP PGUID 12 1 0 0 0 f f f t t f i
12021202
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_ ));
12031203
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_ ));
12041204
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_ ));
1205-
DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID14 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_ ));
1205+
DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID12 1 0 0 0 f f f f t f i s 1 0 1184 "701" _null_ _null_ _null_ _null_ _null_float8_timestamptz _null_ _null_ _null_ ));
12061206
DESCR("convert UNIX epoch to timestamptz");
12071207
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_ ));
12081208
DESCR("transform a time zone adjustment");

‎src/include/utils/timestamp.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -124,6 +124,7 @@ extern Datum timestamp_cmp_timestamptz(PG_FUNCTION_ARGS);
124124
externDatummake_timestamp(PG_FUNCTION_ARGS);
125125
externDatummake_timestamptz(PG_FUNCTION_ARGS);
126126
externDatummake_timestamptz_at_timezone(PG_FUNCTION_ARGS);
127+
externDatumfloat8_timestamptz(PG_FUNCTION_ARGS);
127128

128129
externDatumtimestamptz_eq_timestamp(PG_FUNCTION_ARGS);
129130
externDatumtimestamptz_ne_timestamp(PG_FUNCTION_ARGS);

‎src/test/regress/expected/timestamptz.out

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2307,6 +2307,53 @@ SELECT make_timestamptz(2007, 12, 9, 3, 0, 0, 'VET');
23072307
Sun Dec 09 07:30:00 2007 UTC
23082308
(1 row)
23092309

2310+
SELECT to_timestamp( 0); -- 1970-01-01 00:00:00+00
2311+
to_timestamp
2312+
------------------------------
2313+
Thu Jan 01 00:00:00 1970 UTC
2314+
(1 row)
2315+
2316+
SELECT to_timestamp( 946684800); -- 2000-01-01 00:00:00+00
2317+
to_timestamp
2318+
------------------------------
2319+
Sat Jan 01 00:00:00 2000 UTC
2320+
(1 row)
2321+
2322+
SELECT to_timestamp(1262349296.7890123); -- 2010-01-01 12:34:56.789012+00
2323+
to_timestamp
2324+
-------------------------------------
2325+
Fri Jan 01 12:34:56.789012 2010 UTC
2326+
(1 row)
2327+
2328+
-- edge cases
2329+
SELECT to_timestamp(-1e20::float8); -- error, out of range
2330+
ERROR: timestamp out of range: "-1e+20"
2331+
SELECT to_timestamp(-210866803200.0625); -- error, out of range
2332+
ERROR: timestamp out of range: "-2.10867e+11"
2333+
SELECT to_timestamp(-210866803200); -- 4714-11-24 00:00:00+00 BC
2334+
to_timestamp
2335+
---------------------------------
2336+
Mon Nov 24 00:00:00 4714 UTC BC
2337+
(1 row)
2338+
2339+
-- The upper boundary differs between integer and float timestamps, so check the biggest one
2340+
SELECT to_timestamp(185331707078400::float8); -- error, out of range
2341+
ERROR: timestamp out of range: "1.85332e+14"
2342+
-- nonfinite values
2343+
SELECT to_timestamp(' Infinity'::float);
2344+
to_timestamp
2345+
--------------
2346+
infinity
2347+
(1 row)
2348+
2349+
SELECT to_timestamp('-Infinity'::float);
2350+
to_timestamp
2351+
--------------
2352+
-infinity
2353+
(1 row)
2354+
2355+
SELECT to_timestamp('NaN'::float);
2356+
ERROR: timestamp cannot be NaN
23102357
SET TimeZone to 'Europe/Moscow';
23112358
SELECT '2011-03-26 21:00:00 UTC'::timestamptz;
23122359
timestamptz

‎src/test/regress/sql/timestamptz.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -403,6 +403,21 @@ SELECT '2007-12-09 04:00:00'::timestamp AT TIME ZONE 'VET';
403403
SELECT make_timestamptz(2007,12,9,2,0,0,'VET');
404404
SELECT make_timestamptz(2007,12,9,3,0,0,'VET');
405405

406+
SELECT to_timestamp(0);-- 1970-01-01 00:00:00+00
407+
SELECT to_timestamp(946684800);-- 2000-01-01 00:00:00+00
408+
SELECT to_timestamp(1262349296.7890123);-- 2010-01-01 12:34:56.789012+00
409+
-- edge cases
410+
SELECT to_timestamp(-1e20::float8);-- error, out of range
411+
SELECT to_timestamp(-210866803200.0625);-- error, out of range
412+
SELECT to_timestamp(-210866803200);-- 4714-11-24 00:00:00+00 BC
413+
-- The upper boundary differs between integer and float timestamps, so check the biggest one
414+
SELECT to_timestamp(185331707078400::float8);-- error, out of range
415+
-- nonfinite values
416+
SELECT to_timestamp(' Infinity'::float);
417+
SELECT to_timestamp('-Infinity'::float);
418+
SELECT to_timestamp('NaN'::float);
419+
420+
406421
SET TimeZone to'Europe/Moscow';
407422

408423
SELECT'2011-03-26 21:00:00 UTC'::timestamptz;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp