Do not test this on https://sqliteonline.com, their timestamp datatype is somehow messed up. (rounding down timestamp values at insertion time).
This example is using Postgres 12.2 in Dbeaver, which should be very strict with following SQL standards.
GROUP BY truncated (truncated is a column alias) not error?
At runtime during GROUP BY, it should not know the aliased columns in SELECT clause yet, much less the DATE_TRUNC calculation, yet truncated which is an alias can be used in GROUP BY?
I expected the correct way to require the full calculation in SELECT to be repeated like
GROUP BY date_trunc('day',created_at)
Is truncated somehow being automatically translated to
date_trunc('day',created_at) at the backend?
I definitely remember learning from DQ somewhere that the full calculation needs to be specified in GROUP BY and aliases won’t work
DROP TABLE IF exists post_events; CREATE TABLE post_events( user_id INTEGER, created_at timestamp, event_name VARCHAR ); insert into post_events VALUES (123,'2019-01-01T10:00','enter'), (123,'2019-01-01T11:00','post'), (456,'2019-02-02T7:00','enter'), (456,'2019-02-02T8:00','cancel'), (456,'2019-02-02T9:00','enter'), (456,'2019-02-02T19:00','post')
SELECT date_trunc('day',created_at) truncated,COUNT(user_id ) FROM post_events GROUP BY truncated