Time zones and intervals: 1Z0-171 practice questions
Questions in the style of exam 1Z0-171 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?
- 2024-02-29
- 2024-03-02
- 2024-02-28
- 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?
- 0
- NULL
- An error
- 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;- same
- different
- NULL
- 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;- 02:00
- 14:00
- 03:00
- 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.