Oracle enhanced time type and postgresql time type

Welcome to the Oracle community forum and interact with 2 million technical staff to enter 1rowcreated. SQLselect * fromtest_zone; TIMESTAMP_DTZONE_DTLOCAL_ZONE_DT Login -------------------------------------------------------------------------------------------------------

Welcome to the Oracle community forum and interact with 2 million technical staff> go to 1 row created. SQL select * from test_zone; TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT begin certificate -------------------------------------------------------------------------------------------------------

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

1 row created.
SQL> select * from test_zone;
TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT
---------------------------------------------------------------------------------------------------------
06-SEP-12 01.50.10.000000 PM 06-SEP-12 01.50.10.000000 PM + 08:00 06-SEP-12 01.50.10.000000 PM
01-JAN-09 12.00.00.000000 AM 01-JAN-09 12.00.00.000000 AM + 08:00 01-JAN-09 05.01.01.000000 PM
SQL> alter session set time_zone = '+ 05:00 ';
Session altered.
SQL> select * from test_zone;
TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT (three hours in advance)
---------------------------------------------------------------------------------------------------------
06-SEP-12 01.50.10.000000 PM 06-SEP-12 01.50.10.000000 PM + 08:00 06-SEP-12 10.50.10.000000 AM
01-JAN-09 12.00.00.000000 AM 01-JAN-09 12.00.00.000000 AM + 08:00 01-JAN-09 02.01.01.000000 PM
SQL> select * from v $ timezone_names where rownum <10; -- view the time zone name
TZNAME TZABBREV
--------------------------------------------------------------------------------------------------------------------------------
Africa/Abidjan LMT
Africa/Abidjan GMT
Africa/Accra LMT
Africa/Accra GMT
Africa/Accra GHST
Africa/Addis_Ababa LMT
Africa/Addis_Ababa ADMT
Africa/Addis_Ababa EAT
Africa/Algiers LMT
SQL> select tz_offset ('Africa/accra') from dual;
TZ_OFFS
-------
+ 00: 00
Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss'; -- set the session date display format
SQL> select to_timestamp ('1970 00:01:02. 123', 'yyyymmdd hh24: mi: ss. ff') from dual; -- use the to_timestamp Function
TO_TIMESTAMP ('2017: 01: 808080', 'yyyymmddhh24: MI: SS. ff ')
---------------------------------------------------------------------------
09-SEP-12 12.01.02.123456789 AM
SQL> select to_timestamp_tz ('2017 00:01:02. 20120909 ', 'yyyymmdd hh24: mi: ss. ff tzh: tzm') from dual; -- use to_timestamp_tz
TO_TIMESTAMP_TZ ('1970: 01: 2012090900: 0', 'yyyymmddhh24: MI: SS. FFTZH: T
---------------------------------------------------------------------------
09-SEP-12 12.01.02.123456789 AM + 00:00
Postgresql time type: 9.1.2
Name bucket description minimum value maximum value resolution
Timestamp [No Time Zone] 8 bytes including Date and Time 4713 BC 5874897AD 1 millisecond/14 bits
Timestamp [with time zone] with time zone 8-byte date and time, with time zone 4713 BC 5874897AD 1 millisecond/14 bits
Interval 12-byte interval-178000000-178000000-1 millisecond/14 bits
Date 4 byte is only used for the date 4713 BC 32767AD 1 day
Time [No time Zone] 8 bytes is only used for 00:00:00 1 mS/14 characters in a day
Postgres = # select current_timestamp; -- the precision is 6 bits, and the time zone + 8 is displayed, which is consistent with the timestamp with time zone Type of oracle.
Now
-------------------------------
14:04:51. 363932 + 08
Postgres = # create table test_t (time_col time, date_col date, timestamp_col timestamp); Type difference
CREATE TABLE
Postgres = # insert into test_t values (now (), now (), now ());
INSERT 0 1
Postgres = # select * from test_t;
Time_col | date_col | timestamp_col
----------------- + ------------ + ----------------------------
14:19:24. 277477 | 14:19:24. 277477
(1 row)
Precision and Time Zone Control:
Postgres = # create table test_t1 (time_col time, date_col date, timestamp_col timestamp, timestamp_col0 timestamp (0) without time zone );
CREATE TABLE
Postgres = # insert into test_t1 values (now (), now ());
INSERT 0 1
Postgres = # select * from test_t1;
Time_col | date_col | timestamp_col | timestamp_col0
----------------- + ------------ + ---------------------------- + ---------------------
14:34:59. 840947 | 14:34:59. 840947 | 14:35:00
Postgres = # alter table test_t1 add column timestamp_col1 timestamp (0) with time zone;
ALTER TABLE
Postgres = # insert into test_t1 values (now (), now ());
INSERT 0 1
Postgres = # select * from test_t1;
Time_col | date_col | timestamp_col | timestamp_col0 | timestamp_col1
----------------- + ------------ + ------------------------------ + --------------------- + ------------------------
14:34:59. 840947 | 14:34:59. 840947 | 14:35:00 |
14:36:31. 265579 | 14:36:31. 265579 | 14:36:31 | 14:36:31 + 08
It can be seen that the timestamp itself does not have a time zone, but has a precision. If you need a time zone, add it with time zone.
Postgres = # select now (): timestamp (0) without time zone;
Now
---------------------
14:42:12

[1] [2]