SQL timestamps

PostgreSQL can hold time and date information in the following formats:

• TIME → only time info
• DATE → only date info
• TIMESTAMP → date and time
• TIMESTAMPTZ → date, time, and timezone.

In addition to the types above, there are some functions and operations that are useful when using SQL:

• TIMEZONE
• NOW
• TIMEOFDAY
• CURRENT_TIME
• CURRENT_DATE

An important set of operations includes extracting time based data from a table. This is often done using:

• EXTRACT()
  • Used to “extract” or obtain a sub-component of a date value, including:
    • YEAR
    • MONTH
    • DAY
    • WEEK
    • QUARTER
  • Usage:

EXTRACT(YEAR FROM date_col)1

• AGE()
  • Calculates and returns the current age given a timestamp
  • Usage:

AGE(date_col) # x years y mon z days timedata

• TO_CHAR()
  • General function to convert data types to text
  • TO_CHAR(date_col, 'mm-dd-yyyy)