Advanced SQL Commands

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;