6 -- timestamp, interval arithmetic
9 SELECT timestamp '1996-03-01' - interval '1 second' AS "Feb 29";
10 SELECT timestamp '1999-03-01' - interval '1 second' AS "Feb 28";
11 SELECT timestamp '2000-03-01' - interval '1 second' AS "Feb 29";
12 SELECT timestamp '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
14 CREATE TABLE TEMP_TIMESTAMP (f1 timestamp);
16 -- get some candidate input values
18 INSERT INTO TEMP_TIMESTAMP (f1)
19 SELECT d1 FROM TIMESTAMP_TBL
20 WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997'
21 OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010';
23 SELECT '' AS "15", f1 AS timestamp
27 SELECT '' AS "150", d.f1 AS timestamp, t.f1 AS interval, d.f1 + t.f1 AS plus
28 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
29 ORDER BY plus, timestamp, interval;
31 SELECT '' AS "150", d.f1 AS timestamp, t.f1 AS interval, d.f1 - t.f1 AS minus
32 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
34 ORDER BY minus, timestamp, interval;
36 SELECT '' AS "15", d.f1 AS timestamp, timestamp '1980-01-06 00:00 GMT' AS gpstime_zero,
37 d.f1 - timestamp '1980-01-06 00:00 GMT' AS difference
41 SELECT '' AS "225", d1.f1 AS timestamp1, d2.f1 AS timestamp2, d1.f1 - d2.f1 AS difference
42 FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2
43 ORDER BY timestamp1, timestamp2, difference;
45 SELECT '' as "54", d1 as timestamp,
46 date_part('year', d1) AS year, date_part('month', d1) AS month,
47 date_part('day',d1) AS day, date_part('hour', d1) AS hour,
48 date_part('minute', d1) AS minute, date_part('second', d1) AS second
50 WHERE isfinite(d1) and d1 >= '1-jan-1900 GMT'
54 -- abstime, reltime arithmetic
57 SELECT '' AS four, f1 AS abstime,
58 date_part('year', f1) AS year, date_part('month', f1) AS month,
59 date_part('day',f1) AS day, date_part('hour', f1) AS hour,
60 date_part('minute', f1) AS minute, date_part('second', f1) AS second
62 WHERE isfinite(f1) and f1 <> abstime 'current'
69 SELECT '' AS "15", f1 AS timestamp, date( f1) AS date
71 WHERE f1 <> timestamp 'current'
74 SELECT '' AS "15", f1 AS timestamp, abstime( f1) AS abstime
78 SELECT '' AS four, f1 AS abstime, date( f1) AS date
80 WHERE isfinite(f1) AND f1 <> abstime 'current'
83 SELECT '' AS five, d1 AS timestamp, abstime(d1) AS abstime
84 FROM TIMESTAMP_TBL WHERE NOT isfinite(d1);
86 SELECT '' AS three, f1 as abstime, timestamp(f1) AS timestamp
87 FROM ABSTIME_TBL WHERE NOT isfinite(f1);
89 SELECT '' AS ten, f1 AS interval, reltime( f1) AS reltime
92 SELECT '' AS six, f1 as reltime, interval( f1) AS interval
95 DROP TABLE TEMP_TIMESTAMP;
101 SET DateStyle TO 'US,Postgres';
105 SELECT '' AS "66", d1 AS us_postgres FROM TIMESTAMP_TBL;
107 SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL;
109 SET DateStyle TO 'US,ISO';
111 SELECT '' AS "66", d1 AS us_iso FROM TIMESTAMP_TBL;
113 SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL;
115 SET DateStyle TO 'US,SQL';
119 SELECT '' AS "66", d1 AS us_sql FROM TIMESTAMP_TBL;
121 SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL;
123 SET DateStyle TO 'European,Postgres';
127 INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957');
129 SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957';
131 SELECT '' AS "67", d1 AS european_postgres FROM TIMESTAMP_TBL;
133 SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL;
135 SET DateStyle TO 'European,ISO';
139 SELECT '' AS "67", d1 AS european_iso FROM TIMESTAMP_TBL;
141 SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL;
143 SET DateStyle TO 'European,SQL';
147 SELECT '' AS "67", d1 AS european_sql FROM TIMESTAMP_TBL;
149 SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL;