Interview questions
Dates, times and time zones
junior
1. A date column contains 03/04/2026. How do you parse it?
What it tests: Recognizing ambiguity instead of guessing.
Model answer and scoring
It's ambiguous: 4 March in the US convention, 3 April in the UK convention. I look for a declared locale in the data or the brief and parse with that explicit format.
If no convention is declared, I don't let the parser guess; I quarantine those rows as ambiguous and ask the source.
Strong answers
- Says "ambiguous" and names both readings.
- Uses an explicit format.
Red flags
- Relies on the parser's default or dayfirst guessing.
Practise it: Parse dates by declared locale
mid
2. Some timestamps have an offset and some don't. What do you do with the ones without?
What it tests: Instants versus local times.
Model answer and scoring
A timestamp without an offset isn't an instant; it could be any zone. Unless the rules declare the zone, I quarantine those with a specific reason such as missing_offset, separate from malformed values, and ask the source to send offsets.
Assuming UTC could shift events by hours and break ordering and daily totals.
Strong answers
- Separates missing_offset from bad values.
- Explains the impact of a wrong assumption.
Red flags
- Assumes UTC or server time.
Practise it: Timestamps need an offset
mid
3. Why can sorting timestamps as text pick the wrong latest record?
What it tests: Text versus typed comparison.
Model answer and scoring
Text compares characters, not instants. 2026-02-01T01:00:00+01:00 sorts after 2026-02-01T00:30:00Z as text, but it's earlier as an instant (00:00 UTC). I parse to a timestamp with time zone and compare those values.
Strong answers
- Gives a concrete counterexample.
Red flags
- Thinks ISO format makes text sorting always safe.
senior
4. How do you count events per local day for a user in a time zone with daylight saving time?
What it tests: Time zone conversion and DST awareness.
Model answer and scoring
Store instants in UTC, convert each to the user's IANA time zone (for example America/New_York) at query time, and take the local date. I don't add a fixed offset, because the offset changes at DST transitions; a local day can be 23 or 25 hours long.
I'd test with events around the transition dates.
Strong answers
- Uses IANA zones, not fixed offsets.
- Mentions 23/25-hour days and tests.
Red flags
- Subtracts a fixed number of hours.
mid
5. A birth date stored as a timestamp shows the day before for some users. Why?
What it tests: Date-only values versus instants.
Model answer and scoring
The date was converted to midnight UTC and then displayed in a time zone behind UTC, which moves it to the previous day.
Date-only values should stay as dates without a time zone; I'd store them as DATE and never convert them to instants.
Strong answers
- Explains the zone shift.
- Keeps DATE type.
Red flags
- Adds a day to fix it.
junior
6. A file mixes US and UK slash dates with a locale column. How do you parse them?
What it tests: Per-row locale parsing.
Model answer and scoring
I parse ISO dates for every row, and slash dates using each row's declared locale: month-first for en-US, day-first for en-GB.
Slash dates with an unknown or missing locale are quarantined as ambiguous rather than guessed.
Strong answers
- Uses the row's locale.
- Quarantines ambiguous ones.
Red flags
- Applies dayfirst to the whole column.
Practise it: Parse dates by declared locale
junior
7. How do you handle 2026-02-30 in a date column?
What it tests: Validation of impossible dates.
Model answer and scoring
It isn't a real date, so I quarantine it with an invalid-date reason instead of letting a tool roll it over to March 2.
I check which parser behaviour each tool has, because some lenient parsers shift invalid dates silently.
Strong answers
- Quarantines with a reason.
- Aware of lenient parsers.
Red flags
- Converts it to March 2.
Practise it: Parse dates by declared locale
mid
8. Device timestamps arrive without offsets. The devices are in several countries. What do you do?
What it tests: Refusing to guess time zones.
Model answer and scoring
Without an offset or a declared device time zone, the instant is unknown. I quarantine them and ask for the device time zone or for offsets in the export.
If the owner provides a reliable device-to-zone mapping, I convert with it and keep a note that the zone was inferred.
Strong answers
- Doesn't assume UTC.
- Asks for a mapping or offsets.
Red flags
- Assumes the server's zone.
Practise it: Timestamps need an offset
senior
9. Weekly totals differ between two dashboards. What date-related causes do you check?
What it tests: Calendar definitions.
Model answer and scoring
Week start (Monday or Sunday), ISO week numbering at year boundaries, the time zone used to assign events to days, and whether timestamps were truncated before or after converting to the reporting zone.
I write the definition down and compute both dashboards' weeks from the same UTC instants to show where they diverge.
Strong answers
- Lists week start, ISO weeks, zone.
- Reproduces both definitions.
Red flags
- Says one dashboard is just wrong.
junior
10. How should timestamps be stored and displayed?
What it tests: UTC storage practice.
Model answer and scoring
Store instants in UTC (or with an explicit offset) so they compare correctly, and convert to the viewer's time zone only for display.
Keep date-only values as dates.
Strong answers
- UTC for storage, local for display.
Red flags
- Stores local time text.
Model answers are original study material for practice. They are not employer-endorsed and don't guarantee an outcome.