date_bin or date_bucket postgre SQL

I'm looking to create 30 minute bins from a timestamp field in postgres SQL.

As an example, I have a bus trip which started at 2023-01-01 11.13 UTC time in the trip_time field and there are 1,000 trips for the day all at different times (as an example).

I want to put the above example in a bin time that starts at 11.00 and any bus trip that starts at or after 11.30 goes into the next bin 30 minute bin etc. So above date_bin() field example would show 2023-01-01 11.00, any trips at 2023-01-01 11.43 would be in the 2023-01-01 11.30 bin etc.

I also want to convert the result to a different timezone, as my system stores time in UTC but the trip is in Australia/Darwin time.

Documentation for date_bin() and date_bucket functions look like I have to put in an actual typed date, rather than using my table field as the source or origin field.

Is what I am proposing possible...?

I've tried the following and numerous different variations of below: -

SELECT date_bucket('minute' ,30 ,trip_time AT TIME ZONE 'UTC' AT TIME ZONE 'Australia/Darwin')
FROM table;

SELECT date_bin('30 minute' ,trip_time AT TIME ZONE 'UTC' AT TIME ZONE 'Australia/Darwin' ,TIMEZONE '1970-01-01' )
FROM table;

I keep getting "No function matches.." errors.

1 Answer

As seen in the documentation function date_bin expects an interval, a timestamp or timestamptz and another timestamp or timestamptz but not what you called 'timezone' in the third argument. You can use simply epoch for 1970-01-01T00:00 UTC. AT TIME ZONE 'Australia/Darwin' is to be placed after the function in order to affect the function result. Here is it fixed.

SELECT date_bin('30 minute', trip_time, 'epoch') AT TIME ZONE 'Australia/Darwin'
from the_table;

Please note that date_bin does not exist before PG14. Here is an implementation for earlier versions of Postgres.