Date Manipulation Functions

Topic
Materials

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_unixtimeto_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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

  • 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

Copy to clipboard

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

Copy to clipboard

current_date
2020-12-01
%%sql

SELECT current_timestamp AS current_timestamp

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%sql

SELECT current_date + INTERVAL '32 DAYS' AS result

Copy to clipboard

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

Copy to clipboard

result
2021-01-02 00:00:00
%%sql

SELECT current_date + INTERVAL '730 DAYS' AS result

Copy to clipboard

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

Copy to clipboard

result
2022-12-01 00:00:00
%%sql

SELECT current_date + INTERVAL '-730 DAYS' AS result

Copy to clipboard

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

Copy to clipboard

result
2018-12-02 00:00:00
%%sql

SELECT current_date - INTERVAL '730 DAYS' AS result

Copy to clipboard

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

Copy to clipboard

result
2018-12-02 00:00:00
%%sql

SELECT current_date + INTERVAL '3 MONTHS' AS result

Copy to clipboard

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

Copy to clipboard

result
2021-03-01 00:00:00
%%sql

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

Copy to clipboard

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

Copy to clipboard

result
2019-04-30 00:00:00
%%sql

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

Copy to clipboard

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

Copy to clipboard

result
2019-05-03 03:00:00
%%sql

SELECT current_timestamp + INTERVAL '3 MONTHS' AS result

Copy to clipboard

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

Copy to clipboard

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

SELECT current_timestamp + INTERVAL '10 HOURS' AS result

Copy to clipboard

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

Copy to clipboard

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

SELECT current_timestamp + INTERVAL '10 MINUTES' AS result

Copy to clipboard

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

Copy to clipboard

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

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

Copy to clipboard

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

Copy to clipboard

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

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

Copy to clipboard

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

Copy to clipboard

result
454
%%sql

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

Copy to clipboard

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

Copy to clipboard

result
-454
%%sql

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

Copy to clipboard

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

Copy to clipboard

result
612
%%sql

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%sql

SELECT date_trunc('YEAR', current_date) AS year_beginning

Copy to clipboard

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

Copy to clipboard

year_beginning
2020-01-01 00:00:00+00:00
%%sql

SELECT date_trunc('MONTH', current_date) AS month_beginning

Copy to clipboard

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

Copy to clipboard

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

SELECT date_trunc('WEEK', current_date) AS week_beginning

Copy to clipboard

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

Copy to clipboard

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

SELECT date_trunc('DAY', current_date) AS day_beginning

Copy to clipboard

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

Copy to clipboard

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

SELECT date_trunc('HOUR', current_timestamp) AS hour_beginning

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%sql

SELECT current_timestamp AS current_timestamp

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

current_month
202012
%%sql

SELECT to_char(current_timestamp, 'yyyyMMdd') AS current_date

Copy to clipboard

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

Copy to clipboard

current_date
20201201
%%sql

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%sql

SELECT extract(century FROM current_date) AS century

Copy to clipboard

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

Copy to clipboard

century
21.0
%%sql

SELECT date_part('century', current_date) AS century

Copy to clipboard

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

Copy to clipboard

century
21.0
%%sql

SELECT extract(decade FROM current_date) AS decade

Copy to clipboard

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

Copy to clipboard

decade
202.0
%%sql

SELECT date_part('decade', current_date) AS century

Copy to clipboard

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

Copy to clipboard

century
202.0
%%sql

SELECT extract(year FROM current_date) AS year

Copy to clipboard

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

Copy to clipboard

year
2020.0
%%sql

SELECT extract(quarter FROM current_date) AS quarter

Copy to clipboard

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

Copy to clipboard

quarter
4.0
%%sql

SELECT extract(month FROM current_date) AS month

Copy to clipboard

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

Copy to clipboard

month
12.0
%%sql

SELECT extract(week FROM current_date) AS week

Copy to clipboard

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

Copy to clipboard

week
49.0
%%sql

SELECT extract(day FROM current_date) AS day

Copy to clipboard

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

Copy to clipboard

day
1.0
%%sql

SELECT extract(doy FROM current_date) AS day_of_year

Copy to clipboard

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

Copy to clipboard

day_of_year
336.0
%%sql

SELECT extract(dow FROM current_date) AS day_of_week

Copy to clipboard

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

Copy to clipboard

day_of_week
2.0
%%sql

SELECT extract(hour FROM current_timestamp) AS hour

Copy to clipboard

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

Copy to clipboard

hour
10.0
%%sql

SELECT extract(minute FROM current_timestamp) AS minute

Copy to clipboard

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

Copy to clipboard

minute
55.0
%%sql

SELECT extract(second FROM current_timestamp) AS second

Copy to clipboard

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

Copy to clipboard

second
19.740129
%%sql

SELECT extract(milliseconds FROM current_timestamp) AS millis

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%sql

SELECT extract(epoch FROM current_date) AS date_epoch

Copy to clipboard

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

Copy to clipboard

date_epoch
1606780800.0
%%sql

SELECT date_part('epoch', current_date) AS date_epoch

Copy to clipboard

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

Copy to clipboard

date_epoch
1606780800.0
%%sql

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

Copy to clipboard

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

Copy to clipboard

unixtime
1556648331.0
%%sql

SELECT to_timestamp(1556662731) AS time_from_epoch

Copy to clipboard

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

Copy to clipboard

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

SELECT to_timestamp(1556662731)::date AS time_from_epoch

Copy to clipboard

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

Copy to clipboard

time_from_epoch
2019-04-30
%%sql

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

Copy to clipboard

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

Copy to clipboard

yyyymm_from_epoch
201904