2

I need to summarise a dataset to show the hourly totals per day. The dataset contains travel card transactions for a period of 4 weeks. I would like to see the total transactions per hour (00:00-01:00, 01:00-02:00,...23:00-00:00) for each of the 28 days.

If this is achievable I would hope to resample this to show the average transactions per hour for weekday and weekend, respectively.

The datetime field is datatype timestamp without timezone i.e. 'YYYY-MM-DD HH:MM:SS'.

Is this something PostgreSQL can accomplish?

0
2

date_tunc() is the key.

Transactions per hour

SELECT date_trunc('hour', datetime) AS hour
     , count(*) AS transactons
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Average transactions per hour

... for weekday and weekend, respectively.

SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend
     , round(avg(transactions), 2) AS avg_transactions
FROM  (
   SELECT date_trunc('hour', datetime) AS hour
        , count(*) AS transactions
   FROM   tbl
   GROUP  BY 1
   ) sub
GROUP  BY 1;

0 .. weekday
1 .. weekend

ISODOW produces 1 - 7 for Mon - Sun. Sat & Sun are "weekend". Integer division x/6 returns 0 for weekdays and 1 for weekend.

Include hours without activity

Above queries only observe hours with actual transactions. To include hours without transactions (no rows int the table) with a value of 0 transactions, (outer) join to a an hourly raster / grid for all of the given period of 28 days. And use COALESCE(). Like:

SELECT extract('ISODOW' from hour)::int/6 AS weekday_weekend
     , round(avg(COALESCE(transactions, 0)), 2) AS avg_transactions
FROM  (
   SELECT generate_series(timestamp '2020-12-01' -- first day
                        , timestamp '2020-12-28' -- last day
                          + interval '1 day - 1 hour'
                        , interval '1 hour')
   ) grid(hour)
LEFT JOIN (
   SELECT date_trunc('hour', datetime) AS hour
        , count(*) AS transactions
   FROM   tbl
   GROUP  BY 1
   ) sub USING (hour)
GROUP  BY 1;

The expression timestamp '2020-12-28' + interval '1 day - 1 hour' produces the latest hour of interest for the time series.

db<>fiddle here

Further reading:

2
  • Hi Thank you so much for your help on this. date_trunc provided exactly what I needed. Here is a sample of the results: hour, transactions "2019-09-23 01:00:00" 1 "2019-09-23 02:00:00" 1 "2019-09-23 03:00:00" 9 "2019-09-23 04:00:00" 13 "2019-09-23 05:00:00" 310 "2019-09-23 06:00:00" 2759 "2019-09-23 07:00:00" 11427 I am now trying to further summarise this to show the number of transactions per hour per fare type (demoted as 'fare_id). The 'fare_id' comprises a number of distinct values for each smart card transaction fare type e.g. 24+, 60+, smartlink, etc. Is this achievable? – Nuala Carvill Jan 10 at 14:24
  • @NualaCarvill Guess so. Please ask your new question as question with all relevant details. You can always link to this one for context, and you can post a link forward here. – Erwin Brandstetter Jan 10 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.