Identifying invalid dates in Oracle database due to daylight saving time changes

Question

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?

Answer 1

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

fiddle