Explanation of Timestamp type BC and post-numeric values in Oracle and PostgreSQL and JDBC access

Detailed description of Timestamp type BC and post-numeric values in Oracle and PostgreSQL and JDBC access field: datevalue1: 2010-01-01value2: 2010-01-01BC1. when inserting a value directly into the database: oracle needs to use to_timestamp ('1970-01-01 ', 'yyyy-mm-ddb'). PG can directly use the string '1970-01-01BC '. 2. when querying from the database directly: The date field is directly queried in the oracle database. value1 and value1 show no difference, both of which are 2010-01-01. Use to_char (time, 'dd-mm-yyyybc ', 'nls _ date_language = American ') to display bc and ad. 1 SQL> select time, to_char (time, 'dd-mm-yyyybc ', 'nls _ date_language = American') AS TRUDATE; 2 3 TIME TRUEDATE4---------------------------------------------------------------------------501-JAN-10 12.00.00.000000 AM 01-01-2010bc601-JAN-10 12.00.00.000000 AM 01-01-2010ad in PG: directly query to display BC, as shown below: 1 highgo = # select * from testtime; 2id | time3 ---- + ---------------------- 4 1 | 2 | 2010-01-0 1 00:00:00 BC 3. in java, obtain the string values of value1 and value2 (for copy) and timestamp values (for insert and bind parameters ). Java code: 01System. out. println ("DataType:" + rs. getMetaData (). getColumnTypeName (1); 02 03rs. next (); 04System. out. println ("getString:" + rs. getString (1); 05 Timestamp time1 = rs. getTimestamp (1); 06System. out. println ("getTimestamp:" + time1); 07 08rs. next (); 09System. out. println ("getString:" + rs. getString (1); 10 Timestamp time2 = rs. getTimestamp (1); 11System. out. println ("getTimestamp:" + time2); 12 13 bytes E M. out. println ("time1Compare2Time2:" + time1.compareTo (time2); Oracle output result: DataType: TIMESTAMP getString: 00:00:00. 0 getTimestamp: 00:00:00. 0 getString:-00:00:00. 0 getTimestamp: 00:00:00. 0 time1Compare2Time2: 1 analysis: The result shows that the java string is time-stamped, but the timestamp is not, however, the comparison results are correct (is more promising than BC), but the obtained timestamp is a non-signed, weird value, search for the network, get a qualified explanation for the PG document (although consistent, it cannot be guaranteed to be true, and there is For further verification): If BC has been specified, negate the year and add one for internal storage. (There is no year zero in the Gregorian calendar, so numerically 1 BC becomes year zero .) if BC is declared, calculate the negative number of the Year and add one to save it internally. Because there is no zero year in the Gregorian calendar, the 1BC in the number is a zero year. PG output result: DataType: timestamp getString: 2010-01-02 00:00:00 getTimestamp: 2010-01-02 00:00:00. 0 getString: 00:00:00 BC getTimestamp: 00:00:00. 0 time1Compare2Time2: 1 analysis: the string of this result indicates BC, but the obtained timestamp is no different, however, the comparison results of the two timestamps are still correct (that is, is better than BC ). 4. test Timestamp COPY in PG: copy cannot be used for the Timestamp with a negative sign (that is, the time of BC). Let's test the Timestamp with BC, the result is a timestamp with BC. copy can be used. Time.csv stores the following content and uses copy testtime from 'G:/time.csv 'with csv;. The result is copied successfully and the query data is correct. 1 "3", "00:00:00" 2 "4", "00:00:00 BC"