General

• W3Schools.com -- SQL

  • Keywords

  • Functions

• https://convert.town/column-to-comma-separated-list

PostgreSQL

PostgreSQL 9.6.24 Documentation

JSON

https://www.postgresql.org/docs/9.6/functions-json.html

properties.label::JSONB->>'en' AS property_label

Date and Time

https://www.postgresql.org/docs/9.6/functions-datetime.html

Date Truncation

a field won't need the timestamp indicator, a direct time string does

date_trunc('second', articles.updated_at),

date_trunc('second', TIMESTAMP '2020-12-12 23:49:40')

Time Zone

description_trails.created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin' AS date_description_trail_created_tzde

now() needs only one timezone, because NOW() already carries a time zone with it

now() AT TIME ZONE 'Europe/Berlin' AS created_at_tzde

Interval

now() + INTERVAL '1 day' * 1.5 AS one_and_a_half_day_from_now