Oracle enhanced time type and postgresql time type

Oracle enhanced time type and postgresql time type oracledate include time and date type, including year, month, day, hour, minute, second. This is often used. I believe everyone is familiar with the timestamp type, which is accurate to the subtle time unit. The decimal point of the specified precision. The default value is 6-bit timestamp with time zone. In addition to timestamp, this parameter has a time zone. The timstamp with local time zone type does not contain the time zone offset, which is determined by the user's local session time zone. Interval year to monthinterval day to secondtimestamp you can use to_timestamp_t to convert to timestamp with time zone tz_offset to view the difference of time zone and the database time zone: SELECT dbtimezone FROM dual; select sessiontimezone from dual; SQL> select extract (year from sysdate) from dual; -- get year EXTRACT (YEARFROMSYSDATE) limit 2012 SQL> select extract (month from sysdate) from dual; -- get month EXTRACT (MONTHFROMSYSDATE) explain 9 SQL> select extract (day from sysdate) from dual; -- get day EXTRACT (DAYFROMSYSDATE) ----------------------- 6SQL> select from_tz (timestamp '2017-09-06 09:00:00 ', '8: 00 ') from dual; -- timestamp to timestamp with time zone FROM_TZ (TIMESTAMP '2017-09-2012: 00: 00', '8: 00 ') 201706-sep-12 09.00.00.000000000 AM + 08:00 SQL> create table test_zone 2 (timestamp_dt TIMESTAMP, 3 zone_dt TIMESTAMP WITH TIME ZONE, 4 local_zone_dt TIMESTAMP WITH LOCAL TIME ZONE. SQL> insert into test_zone values (sysdate, sysdate, sysdate); 1 row created. SQL> col TIMESTAMP_DT for a35SQL> col ZONE_DT for a35SQL> col LOCAL_ZONE_DT for a35SQL> select * from test_zone; TIMESTAMP_DT ZONE_DT has been used when there are too PM too many PM + 06-SEP-12 when PMSQL> insert into test_zone values (to_date ('2017010', 'yyyymmdd'), to_timestamp ', 'yyyymmdd'), to_timestamp_tz ('2017 20090101-', 'yyyymmdd hh24miss TZH: tzm'); 1 row created. SQL> select * from test_zone; TIMESTAMP_DT ZONE_DT too far 06-SEP-12 too PM 06-SEP-12 hour PM + 08:00 06-SEP-12 hour PM01-JAN-09 too AM 01-JAN-09 hour AM + 08:00 01-JAN-09 hour PMSQL> 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) please refer to 06-SEP-12 when PM 06-SEP-12 when PM + 08:00 06-SEP-12 10.50.10.000000 AM01-JAN-09 when AM 01-JAN-09 when AM + 08:00 01-JAN-09 02.01.01.000000 pm SQL> select * from v $ hour where rownum <10; -- view the time zone name TZNAME has been removed before Africa/Abidjan networks/Abidjan GMTAfrica/Accra LMTAfrica/Accra networks/Accra GHSTAfrica/Addis_Ababa LMTAfrica/commandid/Algiers LMT ('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 ('2017 00:01:02. 123456789 ', 'yyyymmdd hh24: mi: ss. ff ') from dual; -- The to_timestamp function uses TO_TIMESTAMP ('2017: 01: 66661', 'yyyymmddhh24: MI: SS. FF ') --------------------------------------------------------------------------- 09-SEP-12 12.01.02.123456789 AMSQL> select to_timestamp_tz ('2017 00:01:02. 123456789 ', 'yyyymmdd hh24: mi: ss. ff tzh: tzm') from dual; -- to_timestamp_tz use TO_TIMESTAMP_TZ ('2017: 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 resolution timestamp [No time zone] 8 bytes including Date and time 4713 BC 5874897AD 1 millisecond/14-bit timestamp [time zone] with time zone 8-byte date and time, time zone 4713 BC 5874897AD 1 millisecond/14-bit interval 12-byte time interval-178000000-178000000-year 4713-1 millisecond/14-bit date 4 bytes only for date BC 32767AD 1 day time [No time Zone] 8 bytes is only used for 00:00:00 1 mS/14 bytes S = # select current_timestamp; -- we can see that the precision is 6 bits and the display time zone is + 8, 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 differences CREATE TABLEpostgres = # 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: S = # create table test_t1 (time_col time, date_col date, timestamp_col timestamp, timestamp_col0 timestamp (0) without time zone ); CREATE TABLEpostgres = # 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 | 2012-09-06 14: 35: 00 postgres = # alter table test_t1 add column timestamp_col1 timestamp (0) with time zone; ALTER TABLEpostgres = # insert into test_t1 values (now (), now (), now (); INSERT 0 1 S = # select * from test_t1; time_col | date_col | timestamp_col | timestamp_col0 | timestamp_col1 ------------------- + ------------ + hour + --------------------- + -------------------- 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 contain the time zone, but has the precision. If you need the time zone, add the with time zone to postgres = # select now ():: timestamp (0) without time zone; now ------------------- 2012-09-06 14:42:12