1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
| SQL> with t as (
2 select sysdate as d from dual union all
3 select to_date('31/03/2013 01:59:59','dd/mm/yyyy hh24:mi:ss') from dual union all
4 select to_date('31/03/2013 02:00:01','dd/mm/yyyy hh24:mi:ss') from dual union all
5 select to_date('31/03/2013 02:59:59','dd/mm/yyyy hh24:mi:ss') from dual union all
6 select to_date('31/03/2013 03:00:01','dd/mm/yyyy hh24:mi:ss') from dual union all
7 select to_date('27/10/2013 01:59:59','dd/mm/yyyy hh24:mi:ss') from dual union all
8 select to_date('27/10/2013 02:00:01','dd/mm/yyyy hh24:mi:ss') from dual union all
9 select to_date('27/10/2013 02:59:59','dd/mm/yyyy hh24:mi:ss') from dual union all
10 select to_date('27/10/2013 03:00:01','dd/mm/yyyy hh24:mi:ss') from dual union all
11 select to_date('31/08/2012 16:52:33','dd/mm/yyyy hh24:mi:ss') from dual union all
12 select to_date('31/12/2012 16:52:33','dd/mm/yyyy hh24:mi:ss') from dual
13 ),
14 test as (
15 select d,
16 TO_TIMESTAMP_TZ(to_char(d,'YYYY-MM-DD')||'T'||to_char(d,'HH24:MI:SS')||'.000Z' ||
17 tz_offset('Europe/Paris'),'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"TZH:TZM'
18 ) AT TIME ZONE 'Europe/Paris' as Paris,
19 TO_TIMESTAMP_TZ(to_char(d,'YYYY-MM-DD')||'T'||to_char(d,'HH24:MI:SS')||'.000Z' ||
20 tz_offset('Europe/Paris'),'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"TZH:TZM'
21 ) AT TIME ZONE 'ETC/GMT-1' as gmt_1,
22 TO_TIMESTAMP_TZ(to_char(d,'YYYY-MM-DD')||'T'||to_char(d,'HH24:MI:SS')||'.000Z' ||
23 tz_offset('Europe/Paris'),'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"TZH:TZM'
24 ) AT TIME ZONE 'ETC/GMT-2' as gmt_2
25 from t
26 )
27 select t.*,
28 case when to_char(paris,'hh24') = to_char(gmt_2,'hh24') then 'été'
29 when to_char(paris,'hh24') = to_char(gmt_1,'hh24') then 'hivers'
30 else 'Doh!'
31 end
32 from test t;
D PARIS GMT_1 GMT_2 CASEWHE
------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------
05/09/2013 11:57:31 05/09/13 11:57:31,000000000 EUROPE/PARIS 05/09/13 10:57:31,000000000 ETC/GMT-1 05/09/13 11:57:31,000000000 ETC/GMT-2 été
31/03/2013 01:59:59 31/03/13 00:59:59,000000000 EUROPE/PARIS 31/03/13 00:59:59,000000000 ETC/GMT-1 31/03/13 01:59:59,000000000 ETC/GMT-2 hivers
31/03/2013 02:00:01 31/03/13 01:00:01,000000000 EUROPE/PARIS 31/03/13 01:00:01,000000000 ETC/GMT-1 31/03/13 02:00:01,000000000 ETC/GMT-2 hivers
31/03/2013 02:59:59 31/03/13 01:59:59,000000000 EUROPE/PARIS 31/03/13 01:59:59,000000000 ETC/GMT-1 31/03/13 02:59:59,000000000 ETC/GMT-2 hivers
31/03/2013 03:00:01 31/03/13 03:00:01,000000000 EUROPE/PARIS 31/03/13 02:00:01,000000000 ETC/GMT-1 31/03/13 03:00:01,000000000 ETC/GMT-2 été
27/10/2013 01:59:59 27/10/13 01:59:59,000000000 EUROPE/PARIS 27/10/13 00:59:59,000000000 ETC/GMT-1 27/10/13 01:59:59,000000000 ETC/GMT-2 été
27/10/2013 02:00:01 27/10/13 02:00:01,000000000 EUROPE/PARIS 27/10/13 01:00:01,000000000 ETC/GMT-1 27/10/13 02:00:01,000000000 ETC/GMT-2 été
27/10/2013 02:59:59 27/10/13 02:59:59,000000000 EUROPE/PARIS 27/10/13 01:59:59,000000000 ETC/GMT-1 27/10/13 02:59:59,000000000 ETC/GMT-2 été
27/10/2013 03:00:01 27/10/13 02:00:01,000000000 EUROPE/PARIS 27/10/13 02:00:01,000000000 ETC/GMT-1 27/10/13 03:00:01,000000000 ETC/GMT-2 hivers
31/08/2012 16:52:33 31/08/12 16:52:33,000000000 EUROPE/PARIS 31/08/12 15:52:33,000000000 ETC/GMT-1 31/08/12 16:52:33,000000000 ETC/GMT-2 été
31/12/2012 16:52:33 31/12/12 15:52:33,000000000 EUROPE/PARIS 31/12/12 15:52:33,000000000 ETC/GMT-1 31/12/12 16:52:33,000000000 ETC/GMT-2 hivers
11 rows selected.
SQL> /
D PARIS GMT_1 GMT_2 CASEWHE
------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------
05/12/2013 11:57:58 05/12/13 11:57:58,000000000 EUROPE/PARIS 05/12/13 11:57:58,000000000 ETC/GMT-1 05/12/13 12:57:58,000000000 ETC/GMT-2 hivers
31/03/2013 01:59:59 31/03/13 01:59:59,000000000 EUROPE/PARIS 31/03/13 01:59:59,000000000 ETC/GMT-1 31/03/13 02:59:59,000000000 ETC/GMT-2 hivers
31/03/2013 02:00:01 31/03/13 03:00:01,000000000 EUROPE/PARIS 31/03/13 02:00:01,000000000 ETC/GMT-1 31/03/13 03:00:01,000000000 ETC/GMT-2 été
31/03/2013 02:59:59 31/03/13 03:59:59,000000000 EUROPE/PARIS 31/03/13 02:59:59,000000000 ETC/GMT-1 31/03/13 03:59:59,000000000 ETC/GMT-2 été
31/03/2013 03:00:01 31/03/13 04:00:01,000000000 EUROPE/PARIS 31/03/13 03:00:01,000000000 ETC/GMT-1 31/03/13 04:00:01,000000000 ETC/GMT-2 été
27/10/2013 01:59:59 27/10/13 02:59:59,000000000 EUROPE/PARIS 27/10/13 01:59:59,000000000 ETC/GMT-1 27/10/13 02:59:59,000000000 ETC/GMT-2 été
27/10/2013 02:00:01 27/10/13 02:00:01,000000000 EUROPE/PARIS 27/10/13 02:00:01,000000000 ETC/GMT-1 27/10/13 03:00:01,000000000 ETC/GMT-2 hivers
27/10/2013 02:59:59 27/10/13 02:59:59,000000000 EUROPE/PARIS 27/10/13 02:59:59,000000000 ETC/GMT-1 27/10/13 03:59:59,000000000 ETC/GMT-2 hivers
27/10/2013 03:00:01 27/10/13 03:00:01,000000000 EUROPE/PARIS 27/10/13 03:00:01,000000000 ETC/GMT-1 27/10/13 04:00:01,000000000 ETC/GMT-2 hivers
31/08/2012 16:52:33 31/08/12 17:52:33,000000000 EUROPE/PARIS 31/08/12 16:52:33,000000000 ETC/GMT-1 31/08/12 17:52:33,000000000 ETC/GMT-2 été
31/12/2012 16:52:33 31/12/12 16:52:33,000000000 EUROPE/PARIS 31/12/12 16:52:33,000000000 ETC/GMT-1 31/12/12 17:52:33,000000000 ETC/GMT-2 hivers
11 rows selected.
SQL> |
Partager