-- PostgreSQL time data types
--- TIME: contains only time
--- DATE: contains only date
--- TIMESTAMP: contains date and time
--- TIMESTAMPTZ: contains date, time and timezone
--- related functions and operators: TIMEZONE, NOW, TIMEOFDAY, CURRENT_TIME, CURRENT_DATE

--- show the values of all configuration parameters, with descriptions
SHOW ALL;

--- display the current setting of run-time parameters
SHOW TIMEZONE;

--- date and time functions
SELECT now(), timeofday(), CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP;

------------------------------------------------------------------------------------------

-- extract information from a time based data type using
--- EXTRACT(): retrieves subfields such as year/month/hour from date/time values
--- AGE(): subtract argument from current_date
--- TO_CHAR(): convert various data types (date/time, numeric) to formatted strings, and convert
---- from formatted strings to specific data types.

SELECT extract(year from payment_date) as payment_year,
       extract(quarter from payment_date) as payment_quarter,
       extract(month from payment_date) as payment_month,
       extract(day from payment_date) as payment_day,
       extract(hour from payment_date) as payment_hour,
       extract(minute from payment_date) as payment_minute,
       extract(second from payment_date) as payment_second,
       extract(century from payment_date) as payment_century,
       extract(dow from payment_date) as payment_dow
FROM payment;

--- example result: 16 years 4 mons 22 days 1 hours 34 mins 13.003423 secs
SELECT age(payment_date)
FROM payment;

--- example result: 02-15-2007,THURSDAY /FEBRUARY /2007,   7.99
SELECT to_char(payment_date, 'mm-dd-YYYY'),
       to_char(payment_date, 'DAY/MONTH/YYYY'),
       to_char(amount, '999D00')
FROM payment;

--- During which months did payments occur? Format your answer to return back the full month name.
SELECT distinct to_char(payment_date, 'MONTH')
FROM payment;

--- How many payments occurred on a Monday using extract()
SELECT count(*)
FROM payment
WHERE extract(dow from payment_date) = 1;  -- The day of the week as Monday (1) to Sunday (7)

--- How many payments occurred on a Monday using to_char()
SELECT count(*)
FROM payment
WHERE to_char(payment_date, 'D') = '2';    -- day of the week, Sunday (1) to Saturday (7)

-- date()
select b.starttime, f.name
from bookings as b inner join facilities f on f.facid = b.facid
where f.name like 'Tennis Court%'
and date(b.starttime) = '2012-09-21';