Date Manipulation Functions

Private: Mastering SQL using Postgresql Predefined Functions Date Manipulation Functions

Let us go through some of the important date manipulation functions.

Getting Current Date and Timestamp
Date Arithmetic using INTERVAL and - operator
Getting beginning date or time using date_trunc
Extracting information using to_char as well as calendar functions.
Dealing with unix timestamp using from_unixtime, to_unix_timestamp

Getting Current Date and Timestamp¶

Let us understand how to get the details about current or today’s date as well as current timestamp.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

current_date is the function or operator which will return today’s date.
current_timestamp is the function or operator which will return current time up to milliseconds.
These are not like other functions and do not use () at the end.
There is a format associated with date and timestamp.
- Date - yyyy-MM-dd
- Timestamp - yyyy-MM-dd HH:mm:ss.SSS
We can apply all string manipulation functions on date or timestamp once they are typecasted to strings using varchar.

%%sql

SELECT current_date AS current_date

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_date
2020-12-01

%%sql

SELECT current_timestamp AS current_timestamp

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp
2020-12-01 10:55:19.250677+00:00

Note

Example of applying string manipulation functions on dates. However, it is not a good practice. Postgres provide functions on dates or timestamps for most of the common requirements.

%%sql

SELECT substring(current_date::varchar, 1, 4) AS current_date

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_date
2020

Date Arithmetic¶

Let us understand how to perform arithmetic on dates or timestamps.

We can add or subtract days or months or years from date or timestamp by using special operator called as INTERVAL.
We can also add or subtract hours, minutes, seconds etc from date or timestamp using INTERVAL.
We can combine multiple criteria in one operation using INTERVAL
We can get difference between 2 dates or timestamps using minus (-) operator.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT current_date + INTERVAL '32 DAYS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2021-01-02 00:00:00

%%sql

SELECT current_date + INTERVAL '730 DAYS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2022-12-01 00:00:00

%%sql

SELECT current_date + INTERVAL '-730 DAYS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2018-12-02 00:00:00

%%sql

SELECT current_date - INTERVAL '730 DAYS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2018-12-02 00:00:00

%%sql

SELECT current_date + INTERVAL '3 MONTHS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2021-03-01 00:00:00

%%sql

SELECT '2019-01-31'::date + INTERVAL '3 MONTHS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2019-04-30 00:00:00

%%sql

SELECT '2019-01-31'::date + INTERVAL '3 MONTHS 3 DAYS 3 HOURS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2019-05-03 03:00:00

%%sql

SELECT current_timestamp + INTERVAL '3 MONTHS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2021-03-01 10:55:19.336241+00:00

%%sql

SELECT current_timestamp + INTERVAL '10 HOURS' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2020-12-01 20:55:19.343569+00:00

%%sql

SELECT current_timestamp + INTERVAL '10 MINUTES' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2020-12-01 11:05:19.350628+00:00

%%sql

SELECT current_timestamp + INTERVAL '10 HOURS 10 MINUTES' AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
2020-12-01 21:05:19.357712+00:00

%%sql

SELECT '2019-03-30'::date - '2017-12-31'::date AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
454

%%sql

SELECT '2017-12-31'::date - '2019-03-30'::date AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
-454

%%sql

SELECT current_date - '2019-03-30'::date AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
612

%%sql

SELECT current_timestamp - '2019-03-30'::date AS result

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

result
612 days, 10:55:19.384205

Beginning Date or Time - date_trunc¶

Let us understand how to use date_trunc on dates or timestamps and get beginning date or time.

We can use MONTH to get beginning date of the month.
YEAR can be used to get begining date of the year.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT date_trunc('YEAR', current_date) AS year_beginning

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

year_beginning
2020-01-01 00:00:00+00:00

%%sql

SELECT date_trunc('MONTH', current_date) AS month_beginning

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

month_beginning
2020-12-01 00:00:00+00:00

%%sql

SELECT date_trunc('WEEK', current_date) AS week_beginning

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

week_beginning
2020-11-30 00:00:00+00:00

%%sql

SELECT date_trunc('DAY', current_date) AS day_beginning

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

day_beginning
2020-12-01 00:00:00+00:00

%%sql

SELECT date_trunc('HOUR', current_timestamp) AS hour_beginning

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

hour_beginning
2020-12-01 10:00:00+00:00

Extracting information using to_char¶

Let us understand how to use to_char to extract information from date or timestamp.

Here is how we can get date related information such as year, month, day etc from date or timestamp.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT current_timestamp AS current_timestamp

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp
2020-12-01 10:55:19.457415+00:00

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'yyyy') AS year

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	year
2020-12-01 10:55:19.463947+00:00	2020

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'yy') AS year

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	year
2020-12-01 10:55:19.470978+00:00	20

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'MM') AS month

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	month
2020-12-01 10:55:19.477856+00:00	12

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'dd') AS day_of_month

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	day_of_month
2020-12-01 10:55:19.485328+00:00	01

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'DD') AS day_of_month

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	day_of_month
2020-12-01 10:55:19.492543+00:00	01

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'DDD') AS day_of_year

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	day_of_year
2020-12-01 10:55:19.500876+00:00	336

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'Mon') AS month_name

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	month_name
2020-12-01 10:55:19.508738+00:00	Dec

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'mon') AS month_name

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	month_name
2020-12-01 10:55:19.516104+00:00	dec

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'Month') AS month_name

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	month_name
2020-12-01 10:55:19.524174+00:00	December

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'month') AS month_name

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	month_name
2020-12-01 10:55:19.531890+00:00	december

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'day') AS day_name

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	day_name
2020-12-01 10:55:19.539086+00:00	tuesday

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'DY') AS day_name

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	day_name
2020-12-01 10:55:19.546707+00:00	TUE

Note

When we use Day to get the complete name of a day, it will return 9 character string by padding with spaces.

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'Day') AS dayname

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	dayname
2020-12-01 10:55:19.554521+00:00	Tuesday

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char('2020-11-17'::date, 'Day') AS dayname,
    length(to_char('2020-11-17'::date, 'Day')) AS dayname_length,
    length(trim(to_char('2020-11-17'::date, 'Day'))) AS dayname_trimmed_length

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	dayname	dayname_length	dayname_trimmed_length
2020-12-01 10:55:19.562443+00:00	Tuesday	9	7

Here is how we can get time related information such as hour, minute, seconds, milliseconds etc from timestamp.

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'HH') AS hour24

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	hour24
2020-12-01 10:55:19.569746+00:00	10

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'hh') AS hour12

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	hour12
2020-12-01 10:55:19.578703+00:00	10

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'mm') AS minutes

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	minutes
2020-12-01 10:55:19.588247+00:00	12

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'ss') AS seconds

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	seconds
2020-12-01 10:55:19.595061+00:00	19

%%sql

SELECT current_timestamp AS current_timestamp, 
    to_char(current_timestamp, 'MS') AS millis

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_timestamp	millis
2020-12-01 10:55:19.602141+00:00	602

Here is how we can get the information from date or timestamp in the format we require.

%%sql

SELECT to_char(current_timestamp, 'yyyyMM') AS current_month

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_month
202012

%%sql

SELECT to_char(current_timestamp, 'yyyyMMdd') AS current_date

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_date
20201201

%%sql

SELECT to_char(current_timestamp, 'yyyy/MM/dd') AS current_date

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

current_date
2020/12/01

Extracting information - extract¶

We can get year, month, day etc from date or timestamp using extract function. For almost all these scenarios such as getting year, month, day etc we can use to_char as well.

Let us see the usage of extract to get information such as year, quarter, month, week, day, hour etc.
We can also use date_part in place of extract. However there is subtle difference between them with respect to the syntax.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT extract(century FROM current_date) AS century

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

century
21.0

%%sql

SELECT date_part('century', current_date) AS century

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

century
21.0

%%sql

SELECT extract(decade FROM current_date) AS decade

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

decade
202.0

%%sql

SELECT date_part('decade', current_date) AS century

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

century
202.0

%%sql

SELECT extract(year FROM current_date) AS year

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

year
2020.0

%%sql

SELECT extract(quarter FROM current_date) AS quarter

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

quarter
4.0

%%sql

SELECT extract(month FROM current_date) AS month

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

month
12.0

%%sql

SELECT extract(week FROM current_date) AS week

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

week
49.0

%%sql

SELECT extract(day FROM current_date) AS day

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

day
1.0

%%sql

SELECT extract(doy FROM current_date) AS day_of_year

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

day_of_year
336.0

%%sql

SELECT extract(dow FROM current_date) AS day_of_week

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

day_of_week
2.0

%%sql

SELECT extract(hour FROM current_timestamp) AS hour

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

hour
10.0

%%sql

SELECT extract(minute FROM current_timestamp) AS minute

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

minute
55.0

%%sql

SELECT extract(second FROM current_timestamp) AS second

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

second
19.740129

%%sql

SELECT extract(milliseconds FROM current_timestamp) AS millis

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

millis
19747.729

Dealing with Unix Timestamp¶

Let us go through the functions that can be used to deal with Unix Timestamp.

extract with epoch can be used to convert Unix epoch to regular timestamp. We can also use date_part;
to_timestamp can be used to convert timestamp to Unix epoch.
We can get Unix epoch or Unix timestamp by running date '+%s' in Unix/Linux terminal

Let us sww how we can use functions such as extract or to_timestamp to convert between timestamp and Unix timestamp or epoch.

We can unix epoch in Unix/Linux terminal using date '+%s'

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT extract(epoch FROM current_date) AS date_epoch

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

date_epoch
1606780800.0

%%sql

SELECT date_part('epoch', current_date) AS date_epoch

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

date_epoch
1606780800.0

%%sql

SELECT extract(epoch FROM '2019-04-30 18:18:51'::timestamp) AS unixtime

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

unixtime
1556648331.0

%%sql

SELECT to_timestamp(1556662731) AS time_from_epoch

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

time_from_epoch
2019-04-30 22:18:51+00:00

%%sql

SELECT to_timestamp(1556662731)::date AS time_from_epoch

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

time_from_epoch
2019-04-30

%%sql

SELECT to_char(to_timestamp(1556662731), 'yyyyMM')::int AS yyyyMM_from_epoch

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

yyyymm_from_epoch
201904

Previous Topic

Back to Lesson

Next Topic