Tracking: date/time functions

[xiangjinwu]

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

Caveats:

• A month can have 28, 29, 30, or 31 days.
• A day may have 23 or 25 hours during Daylight Saving Time switch.
• The same instant may have a different reading of year (or month, day, hour, minute, second) in different zones.
• PostgreSQL does not handle leap seconds.
• PostgreSQL uses the proleptic Gregorian calendar rather than the Julian calendar for dates before 1582-10-15.
• ISO 8601 year 0 is generally referred to as 1 BC

proto ExprType 101..=108

Cast

• implicit: date -> timestamp -> timestamptz
• implicit: time -> interval
• assign: timestamptz -> timestamp -> date
• assign: interval -> time
• assign: timestamptz -> time
• assign: timestamp -> time

Arithmetic operators

• expr: support atm on date/timestamp/timestampz/interval/time #924

Basic

• age(timestamp, timestamp) -> interval
• age(timestamptz, timestamptz) -> interval
• date_bin(interval, timestamp, timestamp) -> timestamp
• date_bin(interval, timestamptz, timestamptz) -> timestamptz
• date_trunc(field, interval) -> interval
• date_trunc(field, timestamp) -> timestamp
• date_trunc(field, timestamptz, zone) -> timestamptz
• date_part / extract Implement date_part(text, interval)  #8817
• AT TIME ZONE
• to_timestamp(double) -> timestamptz
• justify_days / justify_hours / justify_interval
• make_date / make_timestamp / make_timestamptz / make_time feat(expr): add support for make_date/time/timestamp #14827
• make_interval
• OVERLAP
• isfinite
• feat(expr): Impl abs[interval] #9091 (not in PostgreSQL)

Formatting

• to_char / to_date / to_timestamp in Tracking: string and bytea functions #8831

Current

• current_timestamp / transaction_timestamp() / now() -> timestamptz
• localtimestamp -> timestamp
• current_date -> date / age(timestamp) -> interval / age(timestamptz) -> interval
• localtime -> time

Nonstandard:

• statement_timestamp() -> timestamptz
• clock_timestamp() -> timestamptz / timeofday() -> varchar
• 'now' / 'today' / 'tomorrow' / 'yesterday'

No support

• pg_sleep expr: pg_sleep should not put the thread to sleep #2561 (comment)
• current_time -> timetz
• current_time(int) -> timetz

Aggregate

• sum / avg for interval

Set Returning Functions

• generate_series(start T, stop T, step INTERVAL) -> setof T where T is timestamp / timestamptz

[matanper]

Is it possible to add to the list date_diff?
Current alternative is taking the epoch and then subtracting by given unit you want:

(EXTRACT(EPOCH FROM date2 - date1) / 86400)::int