Date/time types and formats
Exploratory Data Analysis in SQL
Christina Maimone
Data Scientist
Main types
date
- YYYY-MM-DD
- example: 2018-12-30
timestamp
- YYYY-MM-DD HH:MM:SS
- example: 2018-12-30 13:10:04.3
Intervals
interval examples:
6 days 01:48:08
00:51:03
1 day 21:57:47
07:48:46
406 days 00:31:56
Date/time format examples
1pm on January 10, 2018
01/10/18 1:00
10/01/18 01:00:00
01/10/2018 1pm
January 10th, 2018 1pm
10 Jan 2018 1:00
01/10/18 01:00:00
01/10/18 13:00:00
ISO 8601
ISO = International Organization for Standards
YYYY-MM-DD HH:MM:SS
Example: 2018-01-05 09:35:15
UTC and timezones
UTC = Coordinated Universal Time
Timestamp with timezone:
YYYY-MM-DD HH:MM:SS+HH
Example: 2004-10-19 10:23:54+02
Date and time comparisons
Compare with >, <, =
SELECT '2018-01-01' > '2017-12-31';
now(): current timestamp
SELECT now() > '2017-12-31';
Date subtraction
SELECT now() - '2018-01-01';
343 days 21:26:32.710898
SELECT now() - '2015-01-01';
1439 days 21:32:22.616076
Date addition
SELECT '2010-01-01'::date + 1;
2010-01-02
SELECT '2018-12-10'::date + '1 year'::interval;
2019-12-10 00:00:00
SELECT '2018-12-10'::date + '1 year 2 days 3 minutes'::interval ;
2019-12-12 00:03:00
Let's practice!
Exploratory Data Analysis in SQL
