SQL date-time functions

Link: SQL

Get current date time §

• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP or NOW()

SELECT
  message_id,
  sent_date,
  CURRENT_DATE AS current_date,
  CURRENT_TIME AS current_time,
  CURRENT_TIMESTAMP AS current_timestamp
FROM messages
LIMIT 3;

Compare date: Remember to add time §

• When comparing date with >, <, =>, remember to add 00:00:00 in the date format as well:

  WHERE sent_date >= '2024-09-06 00:00:00'

Date-time processing §

Convert string to date-time §

SELECT
  MAX(post_date::DATE)-MIN(post_date::DATE) AS days_between

EXTRACT() parts from date §

• EXTRACT(parts FROM date_var): parts can be YEAR, MONTH, DAY, HOUR, MINUTE

Trucate to time units §

• DATE_TRUNC()

  SELECT
    message_id
    DATE_TRUNC('month', sent_date) AS truncated_to_month

• Set other units to zero

Note that DATE_TRUNC() will NOT remove the smaller time units, instead it justs set them to 0. E.g. `08/03/2024 14:00:00` will be `08/01/2024 00:00:00` after truncating to month

Add or subtract intervals §

• INTERVAL

SELECT
  sent_date,
  sent_date + INTERVAL '2 days' AS add_2days,
  sent_date + INTERVAL '2 hours' AS add_2hours
FROM messages

Format date §

Convert date-time to string §

• TO_CHAR(date, format)

    SELECT
      sent_date,
      TO_CHAR(sent_date, 'YYYY-MM-DD HH:MI:SS AM') AS formatted_12hr

  • 'YYYY-MM-DD HH:MI:SS': ISO format
  • ''Mon DD, YYYY’: shortmonth
  • 'Month DDth, YYYY': long month
  • 'Day': day of week

Convert string to date-time §

• TO_DATE() or ::DATE: note that this will remove the timestamp and make them 00:00:00
• TO_TIMESTAMP() or ::TIMESTAMP

SELECT
  sent_date::DATE as new_date,  --- use ::DATE
  TO_DATE('2023-08-27', 'YYYY-MM-DD') AS converted_date,  --- use TO_DATE()