Working With Dates

Question 1

Default DATE type format for storing date values.

Answer 1

YYYY-MM-DD

It also uses this format for inserting data into a date column.

Question 2

What is the size of store date values?

Answer 2

4 bytes

Question 3

How would you get the current date?

Answer 3

You can use the following functions:

NOW()
CURRENT_DATE()

Now() will return the current date and time.

If the time is not of interest, you can also specify to only return the date with double colons :: to cast a DATETIME value to the DATE value:
NOW()::date;

Using CURRENT_DATE will return the date with the time.

All three of these options will return you the date in the yyyy-mm-dd format. Within PostgreSQL, you can adjust the format of this output if desired.

SELECT NOW();
now
——————————-
2021-09-27 15:22:53.679985+02
(1 row)

SELECT NOW()::date;
now
————
2021-09-27
(1 row)

SELECT CURRENT_DATE;
current_date
————–
2021-09-27
(1 row)

Question 4

How would you output a date value in a specific format?

Answer 4

To output a date value in a specific format, you use the TO_CHAR() function. This function accepts two parameters:

The value you want to format
The template that defines the output format

You can combine TO_CHAR() and the Now() function to convert the current timestamp into the specified format.

SELECT TO_CHAR(NOW()::date, ‘dd/mm/yyyy’);
to_char
————
27/09/2021
(1 row)
You can also display the date in a format like Sep 27, 2021:

SELECT TO_CHAR(NOW():: DATE, ‘Mon dd, yyyy’);
to_char
————–
Sep 27, 2021
(1 row)

The supported values in the to_char() function are as shown below.

YYYY Year in four digits
YYY Last three-digits of a year
YY Last two digits of a year
MONTH Month name in capital letters
Month Month name with the first letter capitalized
month Month name in lowercase letters
MON/Mon/mon Month abbreviation in all capital, first letter capitalized and all lowercase letters, respectively
MM Month number (01-12)
DAY/Day/day Day name in all capital, first letter capitalized and all lowercase letters, respectively
DDD Day of the year (001 to 366)
DD Day of the month (01 to 31)
D Day of the week (Sunday (1) to Saturday (7))
W Week of the month
WW Week of the year

Other examples:

SELECT Orderdetails,OrderDate as ExistingDateformat,
to_char(OrderDate,’DD-MM-YYYY’) As DDMMYYYY,
to_char(OrderDate,’DD-MM-YY’) As DDMMYY,
to_char(OrderDate,’DD.MM.YY’) As “DD.MM.YY”,
to_char(OrderDate,’MM/DD/YYYY’) As “MM/DD/YYYY”,
to_char(OrderDate,’DAY MM/DD/YYYY’) As “MM/DD/YYYY”,
to_char(OrderDate,’DDD MM/DD/YYYY’) As “DDD MM/DD/YYYY”
FROM SalesOrders

Question 5

How would you get the interval between two dates?

Answer 5

You can use the minus (-) operator or the INTERVAL() function along with plus (+) or minus (-) operators.

You can use the minus (-) operator to calculate the difference between two dates.

Using the - operator allows you to calculate things like the difference between the current date and some other date.

SELECT
orderdate,
now() as currentdate,
now() - orderdate as Interval
FROM SalesOrders
WHERE order_id=2;

You can also specify an interval for returning the date after a specified period using the INTERVAL() function.

For example, the below SQL query gives the following values:

• For future date, specify interval value 2 hours from the current timestamp: NOW() + INTERVAL ‘2 hour’
• For future date, specify interval value of 1 day from the current timestamp: NOW() + interval ‘1 day’
• For past date, specify interval value one year from the current timestamp: NOW() – INTERVAL ‘1 year’
• Calculate one-year past the date from the value stored in the [Orderdate] column: orderdate – INTERVAL ‘1 year’

SELECT
(NOW() + INTERVAL ‘2 hour’) AS twohourslater,
(NOW() + INTERVAL ‘1 day’) AS Onedaylater,
(NOW() - INTERVAL’1 year’) AS OneYearBefore,
(Orderdate- INTERVAL ‘1 year’) as Orderdatediff
FROM salesorders;

Question 6

<p>How would you calculate age using date values?</p>

Answer 6

The AGE() function returns the date difference in years, months and days. You can use this function to calculate a person’s age.

Or we can calculate the age at the current date in years, months, and days using the AGE() function. The following statement uses the AGE() function to calculate the age of a publication from our library checkouts tables:

SELECT
                author_name,
                book_title,
                AGE(published_date)
FROM
                checkouts;

With this function we can calculate how old a book in inventory is:

author_name | book_title | age
————-+————+————————-
James Joyce | Ulysses | 99 years 7 mons 25 days
(1 row)

It is important to note that if you pass a single date into the AGE() function, then it will automatically use the current date to subtract and calculate. You are also able to pass two dates into the function to calculate age such as:

SELECT
                author_name,
                book_title,
                AGE('2000-01-01',published_date),
FROM
                checkouts;

Resulting in:

author_name | book_title | age
————-+————+————————–
James Joyce | Ulysses | 77 years 10 mons 27 days
(1 row)

Question 7

How would you extract the year, quarter, month, week, or day from a date value?

Answer 7

Use the EXTRACT() function.

The EXTRACT() function in PostgreSQL allows you to separate the components of date like the year, quarter, month, and day.

The following statement pulls out the year, month, and day from the published date of Ulysses:

SELECT
author_name,
book_title,
EXTRACT(YEAR FROM published_date) AS YEAR,
EXTRACT(MONTH FROM published_date) AS MONTH,
EXTRACT(DAY FROM published_date) AS DAY
FROM
checkouts;

The results will look like the following:

author_name | book_title | year | month | day
————-+————+——+——-+—–
James Joyce | Ulysses | 1922 | 2 | 2
(1 row)

This is a useful function to be aware of when you may only need a portion of a date value for a calculation with your data for example.

Question 8

How would you convert a timestamp into a different timezone?

Answer 8

Use AT TIMEZONE.

Sometimes, you need to convert the timestamp into a different time zone. For example, you can store date values in UTC (Universal time coordinator) and convert the time zone as required.

To check the current time zone, use SHOW TIMEZONE as shown below.

SHOW TIMEZONE;

You can choose the required time zone value from the pg_timezone_names and use AT TIME ZONE for getting the output per the specified time zone.

SELECT ‘2021-06-28 07:09:19’::timestamp AT TIME ZONE ‘America/Chicago’;

SELECT ‘2021-06-28 07:09:19’::timestamp AT TIME ZONE ‘Asia/Qatar’;