DATA2001 · Data Science, Big Data and Data Variety
Timeseries and Health Data
Week 10 handles time-indexed data — almost all data is qualified by time as a point or a period — covering sensor, medical/health and transport series, how temporal data is represented and stored, and basic techniques to analyse and summarise it. It is the first topic after the midterm and part of the final-exam-only content, so the point-vs-sequence storage models and feature ideas here are examined solely in the 50% final.
What this chapter covers
- 01Temporal data motivation; kinds of time — user-defined (decision), valid time, transaction time
- 02Temporal data types (instant/point, interval, period) and statement kinds (current, sequenced, nonsequenced)
- 03Temporal relations: uni-, bi-, tri-temporal, semi-temporal
- 04Representing time in a database: DATE/TIMESTAMP; representing 'till now' (max-timestamp is most used; NULL and 'now' pitfalls)
- 05PostgreSQL range types (daterange) and GiST indexing of ranges for overlap/contains queries
- 06Time-series storage: point-based (one row per timestamp) vs sequence-based (array_agg into arrays, unnest to expand)
- 07Time-series analysis vs forecasting (ARIMA/SARIMA for stationary/seasonal data) and feature extraction/selection/creation
Point-based queries and a feature over a sensor series
- +1Count at-or-above-20 readings. Day 1 {12,15,20,18} has one (20); day 2 {22,19,25} has two (22, 25). Total = 3. In SQL this is a point-based filter: SELECT COUNT(*) FROM Observations WHERE temperature >= 20.
- +1Day 1 range. max = 20, min = 12, so range = 20 - 12 = 8.
- +1Day 2 range. max = 25, min = 19, so range = 25 - 19 = 6.
- +1SQL shape for the per-day feature. Group the point rows by day and take MAX - MIN: SELECT date(ts) AS day, MAX(temperature) - MIN(temperature) AS range FROM Observations GROUP BY date(ts). This computes an engineered feature (daily range) from raw point-based rows.
Key terms
- Valid time vs transaction time
- Valid time is when a fact is true in the modelled world (e.g. an enrolment runs 1 Feb-30 Jun); transaction time is when the database recorded it (e.g. entered 5 Feb, deleted 10 Mar). A bi-temporal relation tracks both.
- Point vs period vs interval
- A point (instant) is a single moment; a period is an anchored span of time (a start and end); an interval is a duration/length without an anchor. Temporal types and queries are built on these.
- Representing 'till now'
- Options for an open-ended end time: a max-timestamp such as 31-12-9999 (most widely used, simulating 'forever'), a min-timestamp, NULL (which complicates comparisons), or setting start = end. In PostgreSQL prefer 'infinity' over 'now', which means transaction start.
- Point-based vs sequence-based storage
- Point-based keeps one row per timestamp (simple, directly queryable). Sequence-based groups a series into one row holding an array of values (built with array_agg, expanded with unnest), useful for per-period vectors.
- Range type and GiST
- PostgreSQL's daterange represents a time interval (inclusive [ ] or exclusive ) bounds); a GiST index on a range column accelerates overlap, contains and comparison predicates.
- Feature extraction vs selection vs creation
- Extraction transforms raw data into new representations (PCA, Fourier, autoencoders); selection keeps the most relevant existing features and drops the rest; creation (feature engineering) builds new informative features, such as a rolling max or a difference from the previous reading.
Timeseries and Health Data FAQ
What is the difference between valid time and transaction time?
Valid time is when a fact was actually true in the real world; transaction time is when the database stored it. They can differ — an enrolment valid from 1 February might be entered on 5 February. A uni-temporal relation tracks one of them, a bi-temporal relation tracks both (each with a start and end), which lets you ask 'what did we believe was true, and when did we record it?'
When should I store a series point-based vs sequence-based?
Use point-based storage (one row per timestamp) as the default: it is simple and lets ordinary WHERE, GROUP BY, MIN/MAX/AVG queries work directly, and timestamps compare with the usual operators. Use sequence-based storage (one row per period holding an array of values, via array_agg) when you want to treat each period as a vector or reduce row counts; unnest expands the arrays back to point form when needed. JSONB is an alternative when each time point carries richer structure.
How do I represent an interval that is still ongoing?
Common approaches are a max-timestamp like 31-12-9999 (the most widely used, standing in for 'forever'), NULL (but comparisons with NULL return false and lose the 'unknown' meaning), a min-timestamp, or a point representation with start = end. In PostgreSQL, avoid 'now' as a stored value because it means the transaction start time; use 'infinity' for an open-ended upper bound.
Can AI help me with time-series and temporal data in DATA2001?
Yes. Sia can explain valid vs transaction time, point-based vs sequence-based storage, range types and GiST indexing, and it can walk you through point-based queries, array_agg/unnest, and simple feature engineering, checking your SQL and arithmetic. Practise on a neutral sensor series. It is a study aid and does not do graded assessment; the University of Sydney academic-integrity policy applies.
Exam move
This is final-exam-only content, so build durable understanding rather than cramming for a quiz. Fix the three time distinctions in your head — valid vs transaction time, and point vs period vs interval — and be able to give an example of each, plus the uni/bi/tri/semi-temporal ladder. Practise both storage models: write point-based queries (WHERE/GROUP BY/MIN/MAX over one-row-per-timestamp data) and the sequence-based pattern (array_agg to collapse a day into an array, unnest to expand it), since the point-vs-sequence duality is the distinctive Week-10 skill. Know the 'till now' representations and why max-timestamp wins, and how a GiST-indexed daterange speeds overlap queries. Learn the analysis-vs-forecasting split (ARIMA/SARIMA) and the extraction/selection/creation trio at a definitional level. Ask Sia to set fresh point-and-sequence drills and simple feature-engineering tasks; confirm exam details on Canvas.
Working through Timeseries and Health Data in DATA2001? Sia is AskSia’s AI Data Science tutor — ask any DATA2001 Timeseries and Health Data question and get a clear, step-by-step explanation grounded in how DATA2001 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
