Date Manipulation Functions¶
Let us go through some of the important date manipulation functions.
Getting Current Date and Timestamp
Date Arithmetic using
INTERVALand-operatorGetting beginning date or time using
date_truncExtracting information using
to_charas 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_dateis the function or operator which will return today’s date.current_timestampis 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-ddTimestamp -
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
INTERVALWe 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
extractto get information such as year, quarter, month, week, day, hour etc.We can also use
date_partin place ofextract. 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.
extractwithepochcan be used to convert Unix epoch to regular timestamp. We can also usedate_part;to_timestampcan 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 |