Re: BUG #17866: behavior does not match documentation - Mailing list pgsql-bugs
| From | Tom Lane |
|---|---|
| Subject | Re: BUG #17866: behavior does not match documentation |
| Date | |
| Msg-id | 1944674.1679596295@sss.pgh.pa.us Whole thread Raw |
| In response to | BUG #17866: behavior does not match documentation (PG Bug reporting form <noreply@postgresql.org>) |
| Responses |
Re: BUG #17866: behavior does not match documentation
|
| List | pgsql-bugs |
PG Bug reporting form <noreply@postgresql.org> writes:
> pgc=# SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
> extract
> ---------------
> 442800.000000
> (1 row)
> But in documentation we can see:
> SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
> Result: 442800
Hmm. That's not wrong exactly, but it's fair to question whether it
satisfies the POLA. This behavior change happened with commit a2da77cdb
(Change return type of EXTRACT to numeric), and it was intentional
according to the commit log:
- Return values when extracting fields with possibly fractional
values, such as second and epoch, now have the full scale that the
value has internally (so, for example, '1.000000' instead of just
'1').
But exactly nothing was mentioned of that in user-facing docs.
I wonder if we should rethink that and have these operations strip
insignificant trailing zeroes. It looks like that could be done as
practically a one-liner change, since int64_div_fast_to_numeric isn't
yet used anywhere except in these datetime extraction functions.
(The test cases that change behavior are either reverting to their
pre-a2da77cdb output, or were newly added in that commit.)
Thoughts?
regards, tom lane
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index a83feea396..a1cc47b5f7 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -4127,9 +4127,9 @@ get_min_scale(NumericVar *var)
int last_digit_pos;
/*
- * Ordinarily, the input value will be "stripped" so that the last
- * NumericDigit is nonzero. But we don't want to get into an infinite
- * loop if it isn't, so explicitly find the last nonzero digit.
+ * Find the last nonzero digit, if any. (If the input has been unpacked
+ * from storage format, there won't be trailing zero digits; but in
+ * general we can't assume that.)
*/
last_digit_pos = var->ndigits - 1;
while (last_digit_pos >= 0 &&
@@ -4243,15 +4243,11 @@ int64_div_fast_to_numeric(int64 val1, int log10val2)
{
Numeric res;
NumericVar result;
- int rscale;
int w;
int m;
init_var(&result);
- /* result scale */
- rscale = log10val2 < 0 ? 0 : log10val2;
-
/* how much to decrease the weight by */
w = log10val2 / DEC_DIGITS;
/* how much is left to divide by */
@@ -4314,7 +4310,9 @@ int64_div_fast_to_numeric(int64 val1, int log10val2)
int64_to_numericvar(val1, &result);
result.weight -= w;
- result.dscale = rscale;
+
+ /* Set dscale to include only nonzero fractional digits */
+ result.dscale = get_min_scale(&result);
res = make_result(&result);
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 28b71d9681..d791c6772e 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -1692,18 +1692,18 @@ SELECT f1,
EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
EXTRACT(EPOCH FROM f1) AS EPOCH
FROM INTERVAL_TBL;
- f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year
|decade | century | millennium | epoch
--------------------------------+-------------+-------------+------------+--------+------+-----+-------+---------+------+--------+---------+------------+-------------------
- @ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0
| 0 | 0 | 0 | 60.000000
- @ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0
| 0 | 0 | 0 | 18000.000000
- @ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0
| 0 | 0 | 0 | 864000.000000
- @ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34
| 3 | 0 | 0 | 1072958400.000000
- @ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0
| 0 | 0 | 0 | 7776000.000000
- @ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0
| 0 | 0 | 0 | -14.000000
- @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0
| 0 | 0 | 0 | 93784.000000
- @ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6
| 0 | 0 | 0 | 189345600.000000
- @ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0
| 0 | 0 | 0 | 12960000.000000
- @ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0
| 0 | 0 | 0 | 13003200.000000
+ f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year |
decade| century | millennium | epoch
+-------------------------------+-------------+-------------+--------+--------+------+-----+-------+---------+------+--------+---------+------------+------------
+ @ 1 min | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
0 | 0 | 0 | 60
+ @ 5 hours | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 1 | 0 |
0 | 0 | 0 | 18000
+ @ 10 days | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 1 | 0 |
0 | 0 | 0 | 864000
+ @ 34 years | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 34 |
3 | 0 | 0 | 1072958400
+ @ 3 mons | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 2 | 0 |
0 | 0 | 0 | 7776000
+ @ 14 secs ago | -14000000 | -14000 | -14 | 0 | 0 | 0 | 0 | 1 | 0 |
0 | 0 | 0 | -14
+ @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000 | 4 | 3 | 2 | 1 | 0 | 1 | 0 |
0 | 0 | 0 | 93784
+ @ 6 years | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 6 |
0 | 0 | 0 | 189345600
+ @ 5 mons | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 2 | 0 |
0 | 0 | 0 | 12960000
+ @ 5 mons 12 hours | 0 | 0 | 0 | 0 | 12 | 0 | 5 | 2 | 0 |
0 | 0 | 0 | 13003200
(10 rows)
SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
@@ -1782,8 +1782,8 @@ SELECT f1,
-- internal overflow test case
SELECT extract(epoch from interval '1000000000 days');
- extract
------------------------
- 86400000000000.000000
+ extract
+----------------
+ 86400000000000
(1 row)
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index c64bcb7c12..390dae701f 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -1118,73 +1118,73 @@ SELECT d1 as "timestamp",
round(extract(julian from d1)) AS julian,
extract(epoch from d1) AS epoch
FROM TIMESTAMP_TBL;
- timestamp | microseconds | milliseconds | seconds | julian | epoch
------------------------------+--------------+--------------+-----------+-----------+---------------------
- -infinity | | | | -Infinity | -Infinity
- infinity | | | | Infinity | Infinity
- Thu Jan 01 00:00:00 1970 | 0 | 0.000 | 0.000000 | 2440588 | 0.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:02 1997 | 2000000 | 2000.000 | 2.000000 | 2450491 | 855595922.000000
- Mon Feb 10 17:32:01.4 1997 | 1400000 | 1400.000 | 1.400000 | 2450491 | 855595921.400000
- Mon Feb 10 17:32:01.5 1997 | 1500000 | 1500.000 | 1.500000 | 2450491 | 855595921.500000
- Mon Feb 10 17:32:01.6 1997 | 1600000 | 1600.000 | 1.600000 | 2450491 | 855595921.600000
- Thu Jan 02 00:00:00 1997 | 0 | 0.000 | 0.000000 | 2450451 | 852163200.000000
- Thu Jan 02 03:04:05 1997 | 5000000 | 5000.000 | 5.000000 | 2450451 | 852174245.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Tue Jun 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450611 | 865963921.000000
- Sat Sep 22 18:19:20 2001 | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001182760.000000
- Wed Mar 15 08:14:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451619 | 953108041.000000
- Wed Mar 15 13:14:02 2000 | 2000000 | 2000.000 | 2.000000 | 2451620 | 953126042.000000
- Wed Mar 15 12:14:03 2000 | 3000000 | 3000.000 | 3.000000 | 2451620 | 953122443.000000
- Wed Mar 15 03:14:04 2000 | 4000000 | 4000.000 | 4.000000 | 2451619 | 953090044.000000
- Wed Mar 15 02:14:05 2000 | 5000000 | 5000.000 | 5.000000 | 2451619 | 953086445.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:00 1997 | 0 | 0.000 | 0.000000 | 2450491 | 855595920.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Tue Jun 10 18:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450611 | 865967521.000000
- Mon Feb 10 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450491 | 855595921.000000
- Tue Feb 11 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450492 | 855682321.000000
- Wed Feb 12 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450493 | 855768721.000000
- Thu Feb 13 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450494 | 855855121.000000
- Fri Feb 14 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450495 | 855941521.000000
- Sat Feb 15 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450496 | 856027921.000000
- Sun Feb 16 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450497 | 856114321.000000
- Tue Feb 16 17:32:01 0097 BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192711279.000000
- Sat Feb 16 17:32:01 0097 | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102029679.000000
- Thu Feb 16 17:32:01 0597 | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323575279.000000
- Tue Feb 16 17:32:01 1097 | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545120879.000000
- Sat Feb 16 17:32:01 1697 | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610906479.000000
- Thu Feb 16 17:32:01 1797 | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455232879.000000
- Tue Feb 16 17:32:01 1897 | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299559279.000000
- Sun Feb 16 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450497 | 856114321.000000
- Sat Feb 16 17:32:01 2097 | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011874321.000000
- Wed Feb 28 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450143 | 825528721.000000
- Thu Feb 29 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450144 | 825615121.000000
- Fri Mar 01 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450145 | 825701521.000000
- Mon Dec 30 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450449 | 851967121.000000
- Tue Dec 31 17:32:01 1996 | 1000000 | 1000.000 | 1.000000 | 2450450 | 852053521.000000
- Wed Jan 01 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450451 | 852139921.000000
- Fri Feb 28 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450509 | 857151121.000000
- Sat Mar 01 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450510 | 857237521.000000
- Tue Dec 30 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450814 | 883503121.000000
- Wed Dec 31 17:32:01 1997 | 1000000 | 1000.000 | 1.000000 | 2450815 | 883589521.000000
- Fri Dec 31 17:32:01 1999 | 1000000 | 1000.000 | 1.000000 | 2451545 | 946661521.000000
- Sat Jan 01 17:32:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451546 | 946747921.000000
- Sun Dec 31 17:32:01 2000 | 1000000 | 1000.000 | 1.000000 | 2451911 | 978283921.000000
- Mon Jan 01 17:32:01 2001 | 1000000 | 1000.000 | 1.000000 | 2451912 | 978370321.000000
+ timestamp | microseconds | milliseconds | seconds | julian | epoch
+-----------------------------+--------------+--------------+---------+-----------+--------------
+ -infinity | | | | -Infinity | -Infinity
+ infinity | | | | Infinity | Infinity
+ Thu Jan 01 00:00:00 1970 | 0 | 0 | 0 | 2440588 | 0
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:02 1997 | 2000000 | 2000 | 2 | 2450491 | 855595922
+ Mon Feb 10 17:32:01.4 1997 | 1400000 | 1400 | 1.4 | 2450491 | 855595921.4
+ Mon Feb 10 17:32:01.5 1997 | 1500000 | 1500 | 1.5 | 2450491 | 855595921.5
+ Mon Feb 10 17:32:01.6 1997 | 1600000 | 1600 | 1.6 | 2450491 | 855595921.6
+ Thu Jan 02 00:00:00 1997 | 0 | 0 | 0 | 2450451 | 852163200
+ Thu Jan 02 03:04:05 1997 | 5000000 | 5000 | 5 | 2450451 | 852174245
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Tue Jun 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450611 | 865963921
+ Sat Sep 22 18:19:20 2001 | 20000000 | 20000 | 20 | 2452176 | 1001182760
+ Wed Mar 15 08:14:01 2000 | 1000000 | 1000 | 1 | 2451619 | 953108041
+ Wed Mar 15 13:14:02 2000 | 2000000 | 2000 | 2 | 2451620 | 953126042
+ Wed Mar 15 12:14:03 2000 | 3000000 | 3000 | 3 | 2451620 | 953122443
+ Wed Mar 15 03:14:04 2000 | 4000000 | 4000 | 4 | 2451619 | 953090044
+ Wed Mar 15 02:14:05 2000 | 5000000 | 5000 | 5 | 2451619 | 953086445
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:00 1997 | 0 | 0 | 0 | 2450491 | 855595920
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Tue Jun 10 18:32:01 1997 | 1000000 | 1000 | 1 | 2450611 | 865967521
+ Mon Feb 10 17:32:01 1997 | 1000000 | 1000 | 1 | 2450491 | 855595921
+ Tue Feb 11 17:32:01 1997 | 1000000 | 1000 | 1 | 2450492 | 855682321
+ Wed Feb 12 17:32:01 1997 | 1000000 | 1000 | 1 | 2450493 | 855768721
+ Thu Feb 13 17:32:01 1997 | 1000000 | 1000 | 1 | 2450494 | 855855121
+ Fri Feb 14 17:32:01 1997 | 1000000 | 1000 | 1 | 2450495 | 855941521
+ Sat Feb 15 17:32:01 1997 | 1000000 | 1000 | 1 | 2450496 | 856027921
+ Sun Feb 16 17:32:01 1997 | 1000000 | 1000 | 1 | 2450497 | 856114321
+ Tue Feb 16 17:32:01 0097 BC | 1000000 | 1000 | 1 | 1686043 | -65192711279
+ Sat Feb 16 17:32:01 0097 | 1000000 | 1000 | 1 | 1756537 | -59102029679
+ Thu Feb 16 17:32:01 0597 | 1000000 | 1000 | 1 | 1939158 | -43323575279
+ Tue Feb 16 17:32:01 1097 | 1000000 | 1000 | 1 | 2121779 | -27545120879
+ Sat Feb 16 17:32:01 1697 | 1000000 | 1000 | 1 | 2340925 | -8610906479
+ Thu Feb 16 17:32:01 1797 | 1000000 | 1000 | 1 | 2377449 | -5455232879
+ Tue Feb 16 17:32:01 1897 | 1000000 | 1000 | 1 | 2413973 | -2299559279
+ Sun Feb 16 17:32:01 1997 | 1000000 | 1000 | 1 | 2450497 | 856114321
+ Sat Feb 16 17:32:01 2097 | 1000000 | 1000 | 1 | 2487022 | 4011874321
+ Wed Feb 28 17:32:01 1996 | 1000000 | 1000 | 1 | 2450143 | 825528721
+ Thu Feb 29 17:32:01 1996 | 1000000 | 1000 | 1 | 2450144 | 825615121
+ Fri Mar 01 17:32:01 1996 | 1000000 | 1000 | 1 | 2450145 | 825701521
+ Mon Dec 30 17:32:01 1996 | 1000000 | 1000 | 1 | 2450449 | 851967121
+ Tue Dec 31 17:32:01 1996 | 1000000 | 1000 | 1 | 2450450 | 852053521
+ Wed Jan 01 17:32:01 1997 | 1000000 | 1000 | 1 | 2450451 | 852139921
+ Fri Feb 28 17:32:01 1997 | 1000000 | 1000 | 1 | 2450509 | 857151121
+ Sat Mar 01 17:32:01 1997 | 1000000 | 1000 | 1 | 2450510 | 857237521
+ Tue Dec 30 17:32:01 1997 | 1000000 | 1000 | 1 | 2450814 | 883503121
+ Wed Dec 31 17:32:01 1997 | 1000000 | 1000 | 1 | 2450815 | 883589521
+ Fri Dec 31 17:32:01 1999 | 1000000 | 1000 | 1 | 2451545 | 946661521
+ Sat Jan 01 17:32:01 2000 | 1000000 | 1000 | 1 | 2451546 | 946747921
+ Sun Dec 31 17:32:01 2000 | 1000000 | 1000 | 1 | 2451911 | 978283921
+ Mon Jan 01 17:32:01 2001 | 1000000 | 1000 | 1 | 2451912 | 978370321
(65 rows)
-- value near upper bound uses special case in code
@@ -1202,9 +1202,9 @@ SELECT extract(epoch from '294270-01-01 00:00:00'::timestamp);
-- another internal overflow test case
SELECT extract(epoch from '5000-01-01 00:00:00'::timestamp);
- extract
---------------------
- 95617584000.000000
+ extract
+-------------
+ 95617584000
(1 row)
-- test edge-case overflow in timestamp subtraction
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 0dd2fe2c82..9f284e5538 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -1241,74 +1241,74 @@ SELECT d1 as "timestamp",
round(extract(julian from d1)) AS julian,
extract(epoch from d1) AS epoch
FROM TIMESTAMPTZ_TBL;
- timestamp | microseconds | milliseconds | seconds | julian | epoch
----------------------------------+--------------+--------------+-----------+-----------+---------------------
- -infinity | | | | -Infinity | -Infinity
- infinity | | | | Infinity | Infinity
- Wed Dec 31 16:00:00 1969 PST | 0 | 0.000 | 0.000000 | 2440588 | 0.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:02 1997 PST | 2000000 | 2000.000 | 2.000000 | 2450491 | 855624722.000000
- Mon Feb 10 17:32:01.4 1997 PST | 1400000 | 1400.000 | 1.400000 | 2450491 | 855624721.400000
- Mon Feb 10 17:32:01.5 1997 PST | 1500000 | 1500.000 | 1.500000 | 2450491 | 855624721.500000
- Mon Feb 10 17:32:01.6 1997 PST | 1600000 | 1600.000 | 1.600000 | 2450491 | 855624721.600000
- Thu Jan 02 00:00:00 1997 PST | 0 | 0.000 | 0.000000 | 2450451 | 852192000.000000
- Thu Jan 02 03:04:05 1997 PST | 5000000 | 5000.000 | 5.000000 | 2450451 | 852203045.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Tue Jun 10 17:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865989121.000000
- Sat Sep 22 18:19:20 2001 PDT | 20000000 | 20000.000 | 20.000000 | 2452176 | 1001207960.000000
- Wed Mar 15 08:14:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451619 | 953136841.000000
- Wed Mar 15 04:14:02 2000 PST | 2000000 | 2000.000 | 2.000000 | 2451619 | 953122442.000000
- Wed Mar 15 02:14:03 2000 PST | 3000000 | 3000.000 | 3.000000 | 2451619 | 953115243.000000
- Wed Mar 15 03:14:04 2000 PST | 4000000 | 4000.000 | 4.000000 | 2451619 | 953118844.000000
- Wed Mar 15 01:14:05 2000 PST | 5000000 | 5000.000 | 5.000000 | 2451619 | 953111645.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:00 1997 PST | 0 | 0.000 | 0.000000 | 2450491 | 855624720.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
- Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
- Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450490 | 855595921.000000
- Mon Feb 10 14:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855613921.000000
- Thu Jul 10 14:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450641 | 868570321.000000
- Tue Jun 10 18:32:01 1997 PDT | 1000000 | 1000.000 | 1.000000 | 2450611 | 865992721.000000
- Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450491 | 855624721.000000
- Tue Feb 11 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450492 | 855711121.000000
- Wed Feb 12 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450493 | 855797521.000000
- Thu Feb 13 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450494 | 855883921.000000
- Fri Feb 14 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450495 | 855970321.000000
- Sat Feb 15 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450496 | 856056721.000000
- Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000
- Tue Feb 16 17:32:01 0097 PST BC | 1000000 | 1000.000 | 1.000000 | 1686043 | -65192682479.000000
- Sat Feb 16 17:32:01 0097 PST | 1000000 | 1000.000 | 1.000000 | 1756537 | -59102000879.000000
- Thu Feb 16 17:32:01 0597 PST | 1000000 | 1000.000 | 1.000000 | 1939158 | -43323546479.000000
- Tue Feb 16 17:32:01 1097 PST | 1000000 | 1000.000 | 1.000000 | 2121779 | -27545092079.000000
- Sat Feb 16 17:32:01 1697 PST | 1000000 | 1000.000 | 1.000000 | 2340925 | -8610877679.000000
- Thu Feb 16 17:32:01 1797 PST | 1000000 | 1000.000 | 1.000000 | 2377449 | -5455204079.000000
- Tue Feb 16 17:32:01 1897 PST | 1000000 | 1000.000 | 1.000000 | 2413973 | -2299530479.000000
- Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450497 | 856143121.000000
- Sat Feb 16 17:32:01 2097 PST | 1000000 | 1000.000 | 1.000000 | 2487022 | 4011903121.000000
- Wed Feb 28 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450143 | 825557521.000000
- Thu Feb 29 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450144 | 825643921.000000
- Fri Mar 01 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450145 | 825730321.000000
- Mon Dec 30 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450449 | 851995921.000000
- Tue Dec 31 17:32:01 1996 PST | 1000000 | 1000.000 | 1.000000 | 2450450 | 852082321.000000
- Wed Jan 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450451 | 852168721.000000
- Fri Feb 28 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450509 | 857179921.000000
- Sat Mar 01 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450510 | 857266321.000000
- Tue Dec 30 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450814 | 883531921.000000
- Wed Dec 31 17:32:01 1997 PST | 1000000 | 1000.000 | 1.000000 | 2450815 | 883618321.000000
- Fri Dec 31 17:32:01 1999 PST | 1000000 | 1000.000 | 1.000000 | 2451545 | 946690321.000000
- Sat Jan 01 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451546 | 946776721.000000
- Sun Dec 31 17:32:01 2000 PST | 1000000 | 1000.000 | 1.000000 | 2451911 | 978312721.000000
- Mon Jan 01 17:32:01 2001 PST | 1000000 | 1000.000 | 1.000000 | 2451912 | 978399121.000000
+ timestamp | microseconds | milliseconds | seconds | julian | epoch
+---------------------------------+--------------+--------------+---------+-----------+--------------
+ -infinity | | | | -Infinity | -Infinity
+ infinity | | | | Infinity | Infinity
+ Wed Dec 31 16:00:00 1969 PST | 0 | 0 | 0 | 2440588 | 0
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:02 1997 PST | 2000000 | 2000 | 2 | 2450491 | 855624722
+ Mon Feb 10 17:32:01.4 1997 PST | 1400000 | 1400 | 1.4 | 2450491 | 855624721.4
+ Mon Feb 10 17:32:01.5 1997 PST | 1500000 | 1500 | 1.5 | 2450491 | 855624721.5
+ Mon Feb 10 17:32:01.6 1997 PST | 1600000 | 1600 | 1.6 | 2450491 | 855624721.6
+ Thu Jan 02 00:00:00 1997 PST | 0 | 0 | 0 | 2450451 | 852192000
+ Thu Jan 02 03:04:05 1997 PST | 5000000 | 5000 | 5 | 2450451 | 852203045
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Tue Jun 10 17:32:01 1997 PDT | 1000000 | 1000 | 1 | 2450611 | 865989121
+ Sat Sep 22 18:19:20 2001 PDT | 20000000 | 20000 | 20 | 2452176 | 1001207960
+ Wed Mar 15 08:14:01 2000 PST | 1000000 | 1000 | 1 | 2451619 | 953136841
+ Wed Mar 15 04:14:02 2000 PST | 2000000 | 2000 | 2 | 2451619 | 953122442
+ Wed Mar 15 02:14:03 2000 PST | 3000000 | 3000 | 3 | 2451619 | 953115243
+ Wed Mar 15 03:14:04 2000 PST | 4000000 | 4000 | 4 | 2451619 | 953118844
+ Wed Mar 15 01:14:05 2000 PST | 5000000 | 5000 | 5 | 2451619 | 953111645
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:00 1997 PST | 0 | 0 | 0 | 2450491 | 855624720
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000 | 1 | 2450490 | 855595921
+ Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000 | 1 | 2450490 | 855595921
+ Mon Feb 10 09:32:01 1997 PST | 1000000 | 1000 | 1 | 2450490 | 855595921
+ Mon Feb 10 14:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855613921
+ Thu Jul 10 14:32:01 1997 PDT | 1000000 | 1000 | 1 | 2450641 | 868570321
+ Tue Jun 10 18:32:01 1997 PDT | 1000000 | 1000 | 1 | 2450611 | 865992721
+ Mon Feb 10 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450491 | 855624721
+ Tue Feb 11 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450492 | 855711121
+ Wed Feb 12 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450493 | 855797521
+ Thu Feb 13 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450494 | 855883921
+ Fri Feb 14 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450495 | 855970321
+ Sat Feb 15 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450496 | 856056721
+ Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450497 | 856143121
+ Tue Feb 16 17:32:01 0097 PST BC | 1000000 | 1000 | 1 | 1686043 | -65192682479
+ Sat Feb 16 17:32:01 0097 PST | 1000000 | 1000 | 1 | 1756537 | -59102000879
+ Thu Feb 16 17:32:01 0597 PST | 1000000 | 1000 | 1 | 1939158 | -43323546479
+ Tue Feb 16 17:32:01 1097 PST | 1000000 | 1000 | 1 | 2121779 | -27545092079
+ Sat Feb 16 17:32:01 1697 PST | 1000000 | 1000 | 1 | 2340925 | -8610877679
+ Thu Feb 16 17:32:01 1797 PST | 1000000 | 1000 | 1 | 2377449 | -5455204079
+ Tue Feb 16 17:32:01 1897 PST | 1000000 | 1000 | 1 | 2413973 | -2299530479
+ Sun Feb 16 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450497 | 856143121
+ Sat Feb 16 17:32:01 2097 PST | 1000000 | 1000 | 1 | 2487022 | 4011903121
+ Wed Feb 28 17:32:01 1996 PST | 1000000 | 1000 | 1 | 2450143 | 825557521
+ Thu Feb 29 17:32:01 1996 PST | 1000000 | 1000 | 1 | 2450144 | 825643921
+ Fri Mar 01 17:32:01 1996 PST | 1000000 | 1000 | 1 | 2450145 | 825730321
+ Mon Dec 30 17:32:01 1996 PST | 1000000 | 1000 | 1 | 2450449 | 851995921
+ Tue Dec 31 17:32:01 1996 PST | 1000000 | 1000 | 1 | 2450450 | 852082321
+ Wed Jan 01 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450451 | 852168721
+ Fri Feb 28 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450509 | 857179921
+ Sat Mar 01 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450510 | 857266321
+ Tue Dec 30 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450814 | 883531921
+ Wed Dec 31 17:32:01 1997 PST | 1000000 | 1000 | 1 | 2450815 | 883618321
+ Fri Dec 31 17:32:01 1999 PST | 1000000 | 1000 | 1 | 2451545 | 946690321
+ Sat Jan 01 17:32:01 2000 PST | 1000000 | 1000 | 1 | 2451546 | 946776721
+ Sun Dec 31 17:32:01 2000 PST | 1000000 | 1000 | 1 | 2451911 | 978312721
+ Mon Jan 01 17:32:01 2001 PST | 1000000 | 1000 | 1 | 2451912 | 978399121
(66 rows)
-- value near upper bound uses special case in code
@@ -1326,9 +1326,9 @@ SELECT extract(epoch from '294270-01-01 00:00:00+00'::timestamptz);
-- another internal overflow test case
SELECT extract(epoch from '5000-01-01 00:00:00+00'::timestamptz);
- extract
---------------------
- 95617584000.000000
+ extract
+-------------
+ 95617584000
(1 row)
-- test edge-case overflow in timestamp subtraction
pgsql-bugs by date: