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

• [x] implicit: date -> timestamp -> timestamptz
• [x] implicit: time -> interval
• [x] assign: timestamptz -> timestamp -> date
• [x] assign: interval -> time
• [x] assign: timestamptz -> time
• [x] assign: timestamp -> time

Arithmetic operators

• [x] #924

Basic

• age(timestamp, timestamp) -> interval
• age(timestamptz, timestamptz) -> interval
• date_bin(interval, timestamp, timestamp) -> timestamp
• date_bin(interval, timestamptz, timestamptz) -> timestamptz
• [x] date_trunc(field, interval) -> interval
• [x] date_trunc(field, timestamp) -> timestamp
• [x] date_trunc(field, timestamptz, zone) -> timestamptz
• [x] date_part / extract #8817
• [x] AT TIME ZONE
• [x] to_timestamp(double) -> timestamptz
• justify_days / justify_hours / justify_interval
• [x] make_date / make_timestamp / make_timestamptz / make_time https://github.com/risingwavelabs/risingwave/pull/14827
• make_interval
• OVERLAP
• isfinite
• #9091 (not in PostgreSQL)

Formatting

• to_char / to_date / to_timestamp in #8831

Current

• [x] 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 https://github.com/risingwavelabs/risingwave/issues/2561#issuecomment-1127386742
• current_time -> timetz
• current_time(int) -> timetz

Aggregate

• [x] 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

[github-actions[bot]]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean. Don't worry if you think the issue is still valuable to continue in the future. It's searchable and can be reopened when it's time. 😄