Advanced SQL Commands - rFronteddu/general_wiki GitHub Wiki

SHOW

The SHOW commands let us visualize parameters.

SHOW ALL

SHOW TIMEZONE

Timestamps and Extract

PostgreSQL can hold date and time information:

  • TIME - Contains only time
  • DATA - Contains only date
  • TIMESTAMP - Contains date and time
  • TIMESTAMPTZ - Contains date, time, and timezone.

There are several functions and operations related to these specific data types:

  • TIMEZONE: Timezone
  • NOW: Timestamp with time zone
  • TIMEOFDAY: Timestamp text
  • CURRENT_TIME: Just time with timezone
  • CURRENT_DATE: Just date.
SELECT NOW()

SELECT CURRENT_DATE()

EXTRACT

Allows you to obtain a sub-component of a date value

  • YEAR, MONTH, DAY, WEEK, QUARTER
EXTRACT(YEAR FROM date_col)

AGE

Calculates and returns the current age given a timestamp

  • AGE(date_col) returns 13 years 1 mon 5 days 01:34:13.003423

TO _CHAR

Convert data types to text.

  • TO_CHAR(date_col, 'mm-dd-yyyy')
SELECT TO_CHAR(payment_Date, 'mon/dd/YYYY'
FROM payment;