PostgreSQL支持的最早时间戳 [英] Earliest Timestamp supported in PostgreSQL
问题描述
我在多个不同的时区（和时间段）中使用不同的数据库，而通常引起问题的一件事就是日期/时间定义。
如果我感觉很好，则取决于RDBMS和该类型的特定存储。
在SQL Server中，我发现了几种计算此基准日期的方法；
SELECT CONVERT（DATETIME ，0）
或
SELECT DATEADD（MONTH，0，0）
甚至是演员表
DECLARE @ 300 BINARY（8）
SET @ 300 = 0x00000000 + CAST（300 AS BINARY（4 ））
set @ dt = {SELECT CAST（@ 300 AS DATETIME）AS BASEDATE）
print CAST（@dt AS NVARCHAR（100））
（其中@dt是日期时间变量）
我的问题是，是否有类似的方式
根据 date 类型，我可以看到支持的最小日期是公元前4713年，但是有一种方法可以像我在SQL Server中一样，以编程方式设置此值（例如，作为格式化的日期字符串）？
手册将值声明为为：
- 低值：4713 BC
- 高值：294276 AD
克里斯（Chris）指出，同时也支持-无穷大。
请参见注释，稍后在手册的同一页中；仅当您使用整数时间戳记时，以上才是正确的，这是所有模糊的最新PostgreSQL版本中的默认值。如有疑问：
SHOW integer_datetimes;
会告诉您。如果您改用浮点日期时间，则范围会变大，精度（非线性）也会变小。
PostgreSQL不仅会以编程方式计算出最小时间戳，还必须让您将0转换为时间戳以获取最小的时间戳，这也不会如果您使用的是浮点日期时间，则非常有意义。您可以使用朱利安日期转换功能，但这可以为您提供时代，而不是最短时间：
postgres =>选择to_timestamp（0）;
to_timestamp
------------------------
1970-01-01 08：00：00 + 08
（1行）
因为它接受负值。您可能认为给定负数maxint是可行的，但是结果令人惊讶，我不知道我们是否在这里潜伏着一个环绕的错误：
postgres =>选择to_timestamp（-922337203685477）;
to_timestamp
---------------------------------
294247-01- 10 12：00：54.775808 + 08
（1行）
postgres =>选择to_timestamp（-92233720368547）;
to_timestamp
---------------------------------
294247-01- 10 12：00：54.775808 + 08
（1行）
postgres =>选择to_timestamp（-9223372036854）;
to_timestamp
------------------------------
294247-01-10 12： 00：55.552 + 08
（1行）
postgres =>选择to_timestamp（-922337203685）;
错误：时间戳超出范围
postgres =>选择to_timestamp（-92233720368）;
to_timestamp
---------------------------------
0954-03- 26 09：50：36 + 07：43：24 BC
（1行）
postgres =>选择to_timestamp（-9223372036）;
to_timestamp
------------------------------
1677-09-21 07： 56：08 + 07：43：24
（1行）
（也许与即使时间戳记这些天以整数形式存储，to_timestamp也要加倍的事实？）。
我认为让时间戳记范围为任何时间戳记可能是最明智的选择不要出错。毕竟，有效时间戳记的范围不是连续的：
postgres => SELECT TIMESTAMP'2000-02-29';
时间戳记
---------------------
2000-02-29 00:00:00
（1行）
postgres => SELECT TIMESTAMP'2001-02-29';
错误：日期/时间字段值超出范围： 2001-02-29
第1行：SELECT TIMESTAMP 2001-02-29；
所以您不能仅仅因为一个值在两个有效时间戳之间就认为它是它自己有效。
I work with different databases in a number of different time zones (and periods of time) and one thing that normally originates problems, is the date/time definition.
For this reason, and since a date is a reference to a starting value, to keep track of how it was calculated, I try to store the base date; i.e.: the minimum date supported in that particular computer/database;
If I am seeing it well, this depends on the RDBMS and on the particular storage of the type. In SQL Server, I found a couple of ways of calculating this "base date";
SELECT CONVERT(DATETIME, 0)
or
SELECT DATEADD(MONTH, 0, 0 )
or even a cast like this:
DECLARE @300 BINARY(8)
SET @300 = 0x00000000 + CAST(300 AS BINARY(4))
set @dt=(SELECT CAST(@300 AS DATETIME) AS BASEDATE)
print CAST(@dt AS NVARCHAR(100))
(where @dt is a datetime variable)
My question is, is there a similar way of calculating the base date in PostgreSQL, i.e.: the value that is the minimum date supported and is on the base of all calculations?
From the description of the date type, I can see that the minimum date supported is 4713 BC, but is there a way of getting this value programmatically (for instance as a formatted date string), as I do in SQL Server?
The manual states the values as:
- Low value: 4713 BC
- High value: 294276 AD
with the caveat, as Chris noted, that -infinity is also supported.
See the note later in the same page in the manual; the above is only true if you are using integer timestamps, which are the default in all vaguely recent versions of PostgreSQL. If in doubt:
SHOW integer_datetimes;
will tell you. If you're using floating point datetimes instead, you get greater range and less (non-linear) precision. Any attempt to work out the minimum programatically must cope with that restriction.
PostgreSQL does not just let you cast zero to a timestamp to get the minimum possible timestamp, nor would this make much sense if you were using floating point datetimes. You can use the julian date conversion function, but this gives you the epoch not the minimum time:
postgres=> select to_timestamp(0);
to_timestamp
------------------------
1970-01-01 08:00:00+08
(1 row)
because it accepts negative values. You'd think that giving it negative maxint would work, but the results are surprising to the point where I wonder if we've got a wrap-around bug lurking here:
postgres=> select to_timestamp(-922337203685477);
to_timestamp
---------------------------------
294247-01-10 12:00:54.775808+08
(1 row)
postgres=> select to_timestamp(-92233720368547);
to_timestamp
---------------------------------
294247-01-10 12:00:54.775808+08
(1 row)
postgres=> select to_timestamp(-9223372036854);
to_timestamp
------------------------------
294247-01-10 12:00:55.552+08
(1 row)
postgres=> select to_timestamp(-922337203685);
ERROR: timestamp out of range
postgres=> select to_timestamp(-92233720368);
to_timestamp
---------------------------------
0954-03-26 09:50:36+07:43:24 BC
(1 row)
postgres=> select to_timestamp(-9223372036);
to_timestamp
------------------------------
1677-09-21 07:56:08+07:43:24
(1 row)
(Perhaps related to the fact that to_timestamp takes a double, even though timestamps are stored as integers these days?).
I think it's possibly wisest to just let the timestamp range be any timestamp you don't get an error on. After all, the range of valid timestamps is not continuous:
postgres=> SELECT TIMESTAMP '2000-02-29';
timestamp
---------------------
2000-02-29 00:00:00
(1 row)
postgres=> SELECT TIMESTAMP '2001-02-29';
ERROR: date/time field value out of range: "2001-02-29"
LINE 1: SELECT TIMESTAMP '2001-02-29';
so you can't assume that just because a value is between two valid timestamps, it is its self valid.
这篇关于PostgreSQL支持的最早时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
