datetime.out

关系型数据库 Postgresql 6.5.2

          |Thu Jan 01 17:32:01 1998 PST   
          |Sat Feb 28 17:32:01 1998 PST   
          |Sun Mar 01 17:32:01 1998 PST   
          |Wed Dec 30 17:32:01 1998 PST   
          |Thu Dec 31 17:32:01 1998 PST   
          |Sun Dec 31 17:32:01 2000 PST   
          |Mon Jan 01 17:32:01 2001 PST   
          |Mon Dec 31 17:32:01 2001 PST   
          |Tue Jan 01 17:32:01 2002 PST   
(61 rows)

QUERY: SELECT '' AS sixtythree, d1 - '1 year'::timespan AS one_year FROM DATETIME_TBL;
sixtythree|one_year                       
----------+-------------------------------
          |invalid                        
          |-infinity                      
          |infinity                       
          |Tue Dec 31 16:00:00 1968 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |invalid                        
          |invalid                        
          |Sat Feb 10 17:32:01.00 1996 PST
          |Sat Feb 10 17:32:02.00 1996 PST
          |Sat Feb 10 17:32:01.40 1996 PST
          |Sat Feb 10 17:32:01.50 1996 PST
          |Sat Feb 10 17:32:01.60 1996 PST
          |Tue Jan 02 00:00:00 1996 PST   
          |Tue Jan 02 03:04:05 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Mon Jun 10 17:32:01 1996 PDT   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:00 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sat Feb 10 09:32:01 1996 PST   
          |Sat Feb 10 09:32:01 1996 PST   
          |Sat Feb 10 09:32:01 1996 PST   
          |Mon Jun 10 18:32:01 1996 PDT   
          |Sat Feb 10 17:32:01 1996 PST   
          |Sun Feb 11 17:32:01 1996 PST   
          |Mon Feb 12 17:32:01 1996 PST   
          |Tue Feb 13 17:32:01 1996 PST   
          |Wed Feb 14 17:32:01 1996 PST   
          |Thu Feb 15 17:32:01 1996 PST   
          |Fri Feb 16 17:32:01 1996 PST   
          |Mon Feb 16 17:32 0098 BC       
          |Thu Feb 16 17:32:01 0096       
          |Tue Feb 16 17:32:01 0596       
          |Sun Feb 16 17:32:01 1096       
          |Thu Feb 16 17:32:01 1696       
          |Tue Feb 16 17:32:01 1796       
          |Sun Feb 16 17:32:01 1896       
          |Fri Feb 16 17:32:01 1996 PST   
          |Thu Feb 16 17:32:01 2096       
          |Tue Feb 28 17:32:01 1995 PST   
          |Tue Feb 28 17:32:01 1995 PST   
          |Wed Mar 01 17:32:01 1995 PST   
          |Sat Dec 30 17:32:01 1995 PST   
          |Sun Dec 31 17:32:01 1995 PST   
          |Mon Jan 01 17:32:01 1996 PST   
          |Wed Feb 28 17:32:01 1996 PST   
          |Fri Mar 01 17:32:01 1996 PST   
          |Mon Dec 30 17:32:01 1996 PST   
          |Tue Dec 31 17:32:01 1996 PST   
          |Thu Dec 31 17:32:01 1998 PST   
          |Fri Jan 01 17:32:01 1999 PST   
          |Fri Dec 31 17:32:01 1999 PST   
          |Sat Jan 01 17:32:01 2000 PST   
(61 rows)

QUERY: SELECT '' AS fifty, d1 - '1997-01-02'::datetime AS diff
   FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
fifty|diff                                  
-----+--------------------------------------
     |@ 9863 days 8 hours ago               
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1.00 secs  
     |@ 39 days 17 hours 32 mins 2.00 secs  
     |@ 39 days 17 hours 32 mins 1.40 secs  
     |@ 39 days 17 hours 32 mins 1.50 secs  
     |@ 39 days 17 hours 32 mins 1.60 secs  
     |@ 0                                   
     |@ 3 hours 4 mins 5 secs               
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 159 days 16 hours 32 mins 1 sec     
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins            
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 39 days 9 hours 32 mins 1 sec       
     |@ 39 days 9 hours 32 mins 1 sec       
     |@ 39 days 9 hours 32 mins 1 sec       
     |@ 159 days 17 hours 32 mins 1 sec     
     |@ 39 days 17 hours 32 mins 1 sec      
     |@ 40 days 17 hours 32 mins 1 sec      
     |@ 41 days 17 hours 32 mins 1 sec      
     |@ 42 days 17 hours 32 mins 1 sec      
     |@ 43 days 17 hours 32 mins 1 sec      
     |@ 44 days 17 hours 32 mins 1 sec      
     |@ 45 days 17 hours 32 mins 1 sec      
     |@ 45 days 17 hours 32 mins 1 sec      
     |@ 308 days 6 hours 27 mins 59 secs ago
     |@ 307 days 6 hours 27 mins 59 secs ago
     |@ 306 days 6 hours 27 mins 59 secs ago
     |@ 2 days 6 hours 27 mins 59 secs ago  
     |@ 1 day 6 hours 27 mins 59 secs ago   
     |@ 6 hours 27 mins 59 secs ago         
     |@ 57 days 17 hours 32 mins 1 sec      
     |@ 58 days 17 hours 32 mins 1 sec      
     |@ 362 days 17 hours 32 mins 1 sec     
     |@ 363 days 17 hours 32 mins 1 sec     
     |@ 1093 days 17 hours 32 mins 1 sec    
     |@ 1094 days 17 hours 32 mins 1 sec    
     |@ 1459 days 17 hours 32 mins 1 sec    
     |@ 1460 days 17 hours 32 mins 1 sec    
(48 rows)

QUERY: SELECT '' AS fortynine, date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
   date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
   date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
   FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
fortynine|year|month|day|hour|minute|  second
---------+----+-----+---+----+------+--------
         |1969|   12| 31|  16|     0|       0
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|1.000001
         |1997|    2| 10|  17|    32|1.999999
         |1997|    2| 10|  17|    32|     1.4
         |1997|    2| 10|  17|    32|     1.5
         |1997|    2| 10|  17|    32|     1.6
         |1997|    1|  2|   0|     0|       0
         |1997|    1|  2|   3|     4|       5
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    6| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       0
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 10|   9|    32|       1
         |1997|    2| 10|   9|    32|       1
         |1997|    2| 10|   9|    32|       1
         |1997|    6| 10|  18|    32|       1
         |1997|    2| 10|  17|    32|       1
         |1997|    2| 11|  17|    32|       1
         |1997|    2| 12|  17|    32|       1
         |1997|    2| 13|  17|    32|       1
         |1997|    2| 14|  17|    32|       1
         |1997|    2| 15|  17|    32|       1
         |1997|    2| 16|  17|    32|       1
         |1997|    2| 16|  17|    32|       1
         |1996|    2| 28|  17|    32|       1
         |1996|    2| 29|  17|    32|       1
         |1996|    3|  1|  17|    32|       1
         |1996|   12| 30|  17|    32|       1
         |1996|   12| 31|  17|    32|       1
         |1997|    1|  1|  17|    32|       1
         |1997|    2| 28|  17|    32|       1
         |1997|    3|  1|  17|    32|       1
         |1997|   12| 30|  17|    32|       1
         |1997|   12| 31|  17|    32|       1
         |1999|   12| 31|  17|    32|       1
         |2000|    1|  1|  17|    32|       1
         |2000|   12| 31|  17|    32|       1
         |2001|    1|  1|  17|    32|       1
(48 rows)

QUERY: SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
   date_part( 'usec', d1) AS usec
   FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
fortynine|quarter|                msec|             usec
---------+-------+--------------------+-----------------
         |      4|                   0|                0
         |      1|                   0|                0
         |      1|0.000999999999917733|0.999999999917733
         |      1|             999.999|           999999
         |      1|                 400|           400000
         |      1|                 500|           500000
         |      1|                 600|           600000
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      2|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      2|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      4|                   0|                0
         |      4|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      1|                   0|                0
         |      4|                   0|                0
         |      4|                   0|                0
         |      4|                   0|                0
         |      1|                   0|                0
         |      4|                   0|                0
         |      1|                   0|                0
(48 rows)