Durga Gadiraju

Data Manipulation Functions in PostgreSQL

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.

In [1]:

%load_ext sql

In [2]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com: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.

In [3]:

%%sql

SELECT current_date AS current_date

1 rows affected.

Out[3]:

current_date
2022-03-14

In [4]:

%%sql

SELECT current_timestamp AS current_timestamp

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[4]:

current_timestamp
2022-03-14 16:16:23.572340+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.

In [5]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[5]:

current_date
2022

Date Arithmetic¶

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

In [6]:

%load_ext sql

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

In [7]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

In [8]:

%%sql

SELECT current_date + INTERVAL '32 DAYS' AS result

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[8]:

result
2022-04-15 00:00:00

In [9]:

%%sql

SELECT current_date + INTERVAL '730 DAYS' AS result

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[9]:

result
2024-03-13 00:00:00

In [10]:

%%sql

SELECT current_date + INTERVAL '-730 DAYS' AS result

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[10]:

result
2020-03-14 00:00:00

In [11]:

%%sql

SELECT current_date - INTERVAL '730 DAYS' AS result

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[11]:

result
2020-03-14 00:00:00

In [12]:

%%sql

SELECT current_date + INTERVAL '3 MONTHS' AS result

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[12]:

result
2022-06-14 00:00:00

In [13]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[13]:

result
2019-04-30 00:00:00

In [14]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[14]:

result
2019-05-03 03:00:00

In [15]:

%%sql

SELECT current_timestamp + INTERVAL '3 MONTHS' AS result

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[15]:

result
2022-06-14 16:16:32.624885+00:00

In [16]:

%%sql

SELECT current_timestamp + INTERVAL '10 HOURS' AS result

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[16]:

result
2022-03-15 02:16:33.289580+00:00

In [17]:

%%sql

SELECT current_timestamp + INTERVAL '10 MINUTES' AS result

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[17]:

result
2022-03-14 16:26:33.896311+00:00

In [18]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[18]:

result
2022-03-15 02:26:34.471283+00:00

In [19]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[19]:

result
454

In [20]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[20]:

result
-454

In [21]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[21]:

result
1080

In [22]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[22]:

result
1080 days, 16:16:37.228818

Beginning Date or Time – date_trunc¶

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

In [23]:

%load_ext sql

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

In [24]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

In [25]:

%%sql

SELECT date_trunc('YEAR', current_date) AS year_beginning

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[25]:

year_beginning
2022-01-01 00:00:00+00:00

In [26]:

%%sql

SELECT date_trunc('MONTH', current_date) AS month_beginning

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[26]:

month_beginning
2022-03-01 00:00:00+00:00

In [27]:

%%sql

SELECT date_trunc('WEEK', current_date) AS week_beginning

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[27]:

week_beginning
2022-03-14 00:00:00+00:00

In [28]:

%%sql

SELECT date_trunc('DAY', current_date) AS day_beginning

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[28]:

day_beginning
2022-03-14 00:00:00+00:00

In [29]:

%%sql

SELECT date_trunc('HOUR', current_timestamp) AS hour_beginning

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[29]:

hour_beginning
2022-03-14 16:00:00+00:00

Extracting information using to_char¶

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

In [30]:

%load_ext sql

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

In [31]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

In [32]:

%%sql

SELECT current_timestamp AS current_timestamp

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[32]:

current_timestamp
2022-03-14 16:16:45.907528+00:00

In [33]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[33]:

current_timestamp	year
2022-03-14 16:16:46.597274+00:00	2022

In [34]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[34]:

current_timestamp	year
2022-03-14 16:16:47.474192+00:00	22

In [35]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[35]:

current_timestamp	month
2022-03-14 16:16:48.503409+00:00	03

In [36]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[36]:

current_timestamp	day_of_month
2022-03-14 16:16:49.188778+00:00	14

In [37]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[37]:

current_timestamp	day_of_month
2022-03-14 16:16:49.838316+00:00	14

In [38]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[38]:

current_timestamp	day_of_year
2022-03-14 16:16:50.455475+00:00	073

In [39]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[39]:

current_timestamp	month_name
2022-03-14 16:16:51.090776+00:00	Mar

In [40]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[40]:

current_timestamp	month_name
2022-03-14 16:16:51.738819+00:00	mar

In [41]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[41]:

current_timestamp	month_name
2022-03-14 16:16:52.291828+00:00	March

In [42]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[42]:

current_timestamp	month_name
2022-03-14 16:16:52.728274+00:00	march

In [43]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[43]:

current_timestamp	day_name
2022-03-14 16:16:53.569978+00:00	monday

In [44]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[44]:

current_timestamp	day_name
2022-03-14 16:16:54.157396+00:00	MON

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

In [45]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[45]:

current_timestamp	dayname
2022-03-14 16:16:55.241189+00:00	Monday

In [46]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[46]:

current_timestamp	dayname	dayname_length	dayname_trimmed_length
2022-03-14 16:16:56.791348+00:00	Tuesday	9	7

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

In [47]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[47]:

current_timestamp	hour24
2022-03-14 16:16:57.976567+00:00	04

In [48]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[48]:

current_timestamp	hour12
2022-03-14 16:16:58.832670+00:00	04

In [49]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[49]:

current_timestamp	minutes
2022-03-14 16:16:59.561280+00:00	03

In [50]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[50]:

current_timestamp	seconds
2022-03-14 16:17:00.417875+00:00	00

In [51]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[51]:

current_timestamp	millis
2022-03-14 16:17:01.165529+00:00	165

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

In [52]:

%%sql

SELECT to_char(current_timestamp, 'yyyyMM') AS current_month

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[52]:

current_month
202203

In [53]:

%%sql

SELECT to_char(current_timestamp, 'yyyyMMdd') AS current_date

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[53]:

current_date
20220314

In [54]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[54]:

current_date
2022/03/14

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.

In [55]:

%load_ext sql

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

In [56]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

In [57]:

%%sql

SELECT extract(century FROM current_date) AS century

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[57]:

century
21.0

In [58]:

%%sql

SELECT date_part('century', current_date) AS century

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[58]:

century
21.0

In [59]:

%%sql

SELECT extract(decade FROM current_date) AS decade

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[59]:

decade
202.0

In [60]:

%%sql

SELECT date_part('decade', current_date) AS century

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[60]:

century
202.0

In [61]:

%%sql

SELECT extract(year FROM current_date) AS year

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[61]:

year
2022.0

In [62]:

%%sql

SELECT extract(quarter FROM current_date) AS quarter

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[62]:

quarter
1.0

In [63]:

%%sql

SELECT extract(month FROM current_date) AS month

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[63]:

month
3.0

In [64]:

%%sql

SELECT extract(week FROM current_date) AS week

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[64]:

week
11.0

In [65]:

%%sql

SELECT extract(day FROM current_date) AS day

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[65]:

day
14.0

In [66]:

%%sql

SELECT extract(doy FROM current_date) AS day_of_year

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[66]:

day_of_year
73.0

In [67]:

%%sql

SELECT extract(dow FROM current_date) AS day_of_week

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[67]:

day_of_week
1.0

In [68]:

%%sql

SELECT extract(hour FROM current_timestamp) AS hour

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[68]:

hour
16.0

In [69]:

%%sql

SELECT extract(minute FROM current_timestamp) AS minute

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[69]:

minute
17.0

In [70]:

%%sql

SELECT extract(second FROM current_timestamp) AS second

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[70]:

second
16.641653

In [71]:

%%sql

SELECT extract(milliseconds FROM current_timestamp) AS millis

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[71]:

millis
17681.042

Dealing with Unix Timestamp¶

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

In [72]:

%load_ext sql

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

In [73]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

In [74]:

%%sql

SELECT extract(epoch FROM current_date) AS date_epoch

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[74]:

date_epoch
1647216000.0

In [75]:

%%sql

SELECT date_part('epoch', current_date) AS date_epoch

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[75]:

date_epoch
1647216000.0

In [76]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[76]:

unixtime
1556648331.0

In [77]:

%%sql

SELECT to_timestamp(1556662731) AS time_from_epoch

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[77]:

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

In [78]:

%%sql

SELECT to_timestamp(1556662731)::date AS time_from_epoch

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[78]:

time_from_epoch
2019-04-30

In [79]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[79]:

yyyymm_from_epoch
201904

Durga Gadiraju

Data Manipulation Functions in PostgreSQL

Date Manipulation Functions¶

Getting Current Date and Timestamp¶

Date Arithmetic¶

Beginning Date or Time – date_trunc¶

Extracting information using to_char¶

Extracting information – extract¶

Dealing with Unix Timestamp¶

Share this post

Join Our Community

Follow Us

Links

Contact Info

Address

Phone

Email