Stepwise-Lab

Time zones and intervals: 1Z0-071 practice questions

Questions in the style of exam 1Z0-071 on this topic. Answer each one first, then open its answer and explanation.

What this topic covers

  • CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP
  • INTERVAL data types

Practice questions

1. What does SELECT DATE '2024-01-31' + INTERVAL '1' MONTH FROM dual return?

  1. 2024-02-29
  2. 2024-03-02
  3. 2024-02-28
  4. It fails: 31 February does not exist (ORA-01839).
Show the answer

Answer: D

Interval arithmetic follows the ANSI rule: it changes the month, keeps the day and does not adjust to the end of the month. 31 February does not exist, so the expression fails with ORA-01839. 2024-02-29 is what ADD_MONTHS(DATE '2024-01-31', 1) returns, and Oracle never rolls over into March (2024-03-02) or picks 28 February.

2. What does SELECT EXTRACT(HOUR FROM DATE '2024-03-31') FROM dual return?

  1. 0
  2. NULL
  3. An error
  4. 24
Show the answer

Answer: C

EXTRACT can take only YEAR, MONTH and DAY from a DATE, even though a DATE holds a time, so asking for HOUR raises ORA-30076: invalid extract field for extract source. It does not return 0 for midnight, or NULL. For the hour, use EXTRACT(HOUR FROM CAST(d AS TIMESTAMP)) or TO_CHAR(d, 'HH24'); 24 is never an hour value.

3. What does this query return?

SELECT CASE WHEN CAST(CURRENT_TIMESTAMP AS TIMESTAMP) = LOCALTIMESTAMP
            THEN 'same' ELSE 'different' END AS r
FROM   dual;
  1. same
  2. different
  3. NULL
  4. ORA-00932: inconsistent datatypes
Show the answer

Answer: A

Both functions read the clock once for the statement and both use the session time zone; LOCALTIMESTAMP is simply CURRENT_TIMESTAMP without its time zone. Casting to TIMESTAMP drops the zone, so the values match. The CAST makes both sides plain TIMESTAMP, so there is no type error.

4. In January Warsaw is one hour ahead of UTC and New York is five hours behind. What does this query return?

SELECT TO_CHAR(TIMESTAMP '2024-01-15 08:00:00 Europe/Warsaw'
               AT TIME ZONE 'America/New_York', 'HH24:MI') AS t
FROM   dual;
  1. 02:00
  2. 14:00
  3. 03:00
  4. 08:00
Show the answer

Answer: A

AT TIME ZONE keeps the instant and changes how it is shown. In January Warsaw is UTC+1 and New York is UTC-5, so 08:00 in Warsaw is 07:00 UTC and 02:00 in New York. 14:00 goes the wrong way, and 08:00 would mean only the label changed.