Shulou(Shulou.com)06/02 Report--

This article introduces the difference between PostgreSQL timestamp and Oracle. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

PG has two timestamps: 1. Timestamp without time zone;2 and timestamp with time zone; are actually no different from each other in internal storage. The internal names are timestamp and timestamptz (the type name question has been mentioned in the previous blog post, please read it yourself), and the OID is 1114 and 1184 respectively.

Their type definitions are:

Flying=# select typname,typlen,typstorage,typinput from pg_type where oid in (1114 and 1184); typname | typlen | typstorage | typinput-+- timestamp | 8 | p | timestamp_in timestamptz | 8 | p | timestamptz_in (2 rows) flying=#

All 8 bytes are saved in microseconds, and the internal definition is

Typedef int64 Timesttypedef int64 TimestampTz;typedef int64 TimeOffset;typedef int32 fsec_t; / * fractional seconds (in microseconds) * / typedef struct {TimeOffset time; / * all time units other than days, months and * years * / int32 day; / * days, after time for alignment * / int32 month; / * months and years, after time for alignment * /} Interval

The first two types correspond to timestamp and timestamptz, respectively, and the last Interval corresponds to the type interval (other articles mentioned that the datum of type interval is a pointer to data, which is it).

Let's take a look at the input function timestamptz_in (src/backend/utils/adt/timestamp.c), which is preceded by parsing (yes, there's a small parser embedded here) to parse strings. If the input format is common to us, such as: 2019-07-09 14 CST 19VR 34.255 CST, it calls tm2timestamp, in which there is an adjustment dt2local based on the time zone. In other words, the last stored time does not record the input time zone.

Let's take a look at Oracle, which has three timestamps: 1, timestamp without time zone;2, timestamp with time zone;3, and timestamp with local time zone.

From the point of view of the document, the last one does not save the input time zone and is adjusted to the database time zone. In fact, this type is more like the timestamp with time zone of PG, while PG has no type that corresponds to the timestamp with time zone of Oralce.

Experience the operation under Oracle first.

SQL > CREATE TABLE t (col1 TIMESTAMP WITH TIME ZONE); table has been created. Is the SQL > desc t name empty? Type-COL1 TIMESTAMP (6) WITH TIME ZONESQL >

The default precision is 6

SQL > INSERT INTO t VALUES ('15-July-19 11.15.46.369000am + 08VR 00'); 1 line has been created. SQL > INSERT INTO t VALUES ('15-July-19 11.15.46.369000am + 0900'); one line has been created. SQL > SELECT * FROM tress COL1MUL1MULFUM 15-July-19 11.15.46.369000 AM + 08RV 0015-July-19 11.15.46.369000 AM + 09:00SQL >

The same operation in PG:

Flying=# CREATE TABLE t (col1 TIMESTAMP WITH TIME ZONE); CREATE TABLEflying=# show timezone; TimeZone- UTC (1 row) flying=# INSERT INTO t VALUES ('2019-7-15 11 flying=# INSERT INTO t VALUES 15 row 46.369000 + 8'); INSERT 0 1flying=# INSERT INTO t VALUES ('2019-7-15 11 Velcro 15 flying=# INSERT INTO t VALUES 46.369000 + 9'); INSERT 0 1flying=# SELECT * FROM t Col1-- 2019-07-1503 flying=# show timezone; TimeZone- UTC 15 flying=# show timezone; TimeZone- UTC 46.3691400 2019-07-1502Vue 46.3691400 (2 row)

The time zone attribute that inserts the data itself has actually disappeared.

It is conceivable that if there is a scenario in which the customer is more interested in the local time of each input time and wants to find out as is when querying, PG can also deal with it, but it obviously has to find some trouble for the application developer.

Custom types can well meet this requirement, whether it is for PG embedding or our own definition of composite types, and we can demonstrate them again when we have the opportunity.

On the PostgreSQL timestamp and Oracle what is different to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.