Suppose I have a database column that stores data in date format. All records currently stored in this table are dates whose time is set to 00:00 AM.
The America/São Paulo region eventually enters daylight saving time. When this happens, the clock is set forward by one hour. The first valid hour of the day that begins daylight saving time is 1:00 AM (that is, there is no 0:00 AM). For example, the date 10/15/2017 0:00 AM is considered invalid due to the implementation of daylight saving time that year (the first valid time is 10/15/2017 1:00 AM).
How to implement a SELECT...WHERE query to identify dates considered invalid due to the start of daylight saving time in the America/São Paulo region?
You can look it up https://www.timeanddate.com/time/change/brazil/sao-paulo?year=2018
| Year | DST End (Clock Backward) | DST Start (Clock Forward) |
|---|---|---|
| 2010 | Sunday, 21 February, 00:00 | Sunday, 17 October, 00:00 |
| 2011 | Sunday, 20 February, 00:00 | Sunday, 16 October, 00:00 |
| 2012 | Sunday, 26 February, 00:00 | Sunday, 21 October, 00:00 |
| 2013 | Sunday, 17 February, 00:00 | Sunday, 20 October, 00:00 |
| 2014 | Sunday, 16 February, 00:00 | Sunday, 19 October, 00:00 |
| 2015 | Sunday, 22 February, 00:00 | Sunday, 18 October, 00:00 |
| 2016 | Sunday, 21 February, 00:00 | Sunday, 16 October, 00:00 |
| 2017 | Sunday, 19 February, 00:00 | Sunday, 15 October, 00:00 |
| 2018 | Sunday, 18 February, 00:00 | Sunday, 4 November, 00:00 |
| 2019 | Sunday, 17 February, 00:00 | No DST Start |
If you want a query then you can generate a list of days and find those where the difference in the hours between the day and the next day is more than 24 hours:
WITH bounds (start_dt, end_dt) AS (
SELECT DATE '2010-01-01',
TRUNC(SYSDATE)
FROM DUAL
),
days (dt, ts) AS (
SELECT start_dt + (LEVEL - 1),
FROM_TZ(CAST(start_dt AS TIMESTAMP), 'America/Sao_Paulo')
+ INTERVAL '1' DAY * (LEVEL - 1)
FROM bounds
CONNECT BY
start_dt + (LEVEL - 1) <= end_dt
),
diff (dt, ts, diff) AS (
SELECT dt,
ts,
LEAD(CAST(ts AS DATE)) OVER (ORDER BY ts) - CAST(ts AS DATE)
FROM days
)
SELECT dt
FROM diff
WHERE diff > 1
Which outputs:
| DT |
|---|
| 2010-10-17 00:00:00 |
| 2011-10-16 00:00:00 |
| 2012-10-21 00:00:00 |
| 2013-10-20 00:00:00 |
| 2014-10-19 00:00:00 |
| 2015-10-18 00:00:00 |
| 2016-10-16 00:00:00 |
| 2017-10-15 00:00:00 |
| 2018-11-04 00:00:00 |