🐘 PostgreSQL - Date and Time
Always use timestamp without time zone and clock_timestamp(). timestamp implicitly includes the without time zone so no need to be extra verbose. clock_timestamp() is superior to now() because now() just tells when the query started. Handle time zones in your application layer.
CREATE TABLE my_table (
my_table_id serial PRIMARY KEY,
created_at timestamp DEFAULT clock_timestamp()
);
Use ISO 8601 1999-01-02 03:04:05+06.
SELECT '2012-08-31 01:00:00'::timestamp without time zone;
SELECT '2012-08-31 01:00:00'::timestamp; -- without time zone is implicit
SELECT timestamp '2012-08-31 01:00:00+02';
Subtracting - timestamps creates interval data types that represents difference between the two timestamps. Note that date data type only counts days, thus does not use interval data type.
SELECT timestamp '2012-08-25 01:00:00' - timestamp '2012-08-20 00:00:00';
-- => 5 days 01:00:00 (interval)
SELECT date '2012-08-25' - date '2012-08-20';
-- => 5 (integer)
SELECT timestamp '2012-08-25 01:00:00' - date '2012-08-20';
-- => 5 days 01:00:00 (interval)
SELECT date '2012-08-25' - timestamp '2012-08-20 00:00:00';
-- => 5 days (interval)
SELECT timestamp '2012-08-20 00:00:00' - timestamp '2012-08-25 01:00:00';
-- => Null, invalid subtraction. (interval)
You can add + intervals to timestamps or integers to dates.
SELECT clock_timestamp() + '1 week' AS a_week_from_now;
SELECT timestamp '2012-08-25 01:55:00' + interval '1 day';
-- => 2012-08-26 01:55:00
SELECT date '2012-08-25' + 1;
-- => 2012-08-26
SELECT date '2012-08-01' - 1;
-- => 2012-07-31
You can multiply * and divide / intervals.
SELECT interval '2 days 01:00:00' * 2;
-- => 4 days 02:00:00 (interval)
SELECT interval '2 days 01:00:00' / 2;
-- => 1 day 00:30:00 (interval)
Creating date ranges with generate_series().
SELECT * FROM generate_series(
'2014-01-01'::timestamp,
'2014-12-01'::timestamp,
'42 days'
);
-- 2014-01-01 00:00:00, 2014-02-12 00:00:00, ..., 2014-10-22 00:00:00
SELECT x AS first_of_the_month
FROM generate_series(
'2014-01-01'::timestamp,
'2014-12-01'::timestamp,
'1 month'
) AS f(x);
-- 2014-01-01 00:00:00, 2014-02-01 00:00:00, ..., 2014-12-01 00:00:00
SELECT generate_series(
timestamp '2012-10-01',
timestamp '2012-10-31',
interval '1 day'
) AS october_date;
-- => 2012-10-01 00:00:00, ..., 2012-10-31 00:00:00
SELECT generate_series(
date '2012-10-01',
date '2012-10-31',
interval '1 day'
)::date AS october_date;
-- => 2012-10-01, ..., 2012-10-31
extract() function allows getting individual parts of timestamps, intervals or dates.
SELECT extract(day FROM timestamp '2012-08-31');
-- => 31 (double precision)
SELECT extract(epoch from timestamp '2001-02-16');
-- => 9.822816E8 (double precision)
SELECT extract(epoch FROM (
timestamp '2012-09-02 00:00:00' - timestamp '2012-08-31 01:00:00'
));
-- => 169200 (double precision)
Generate months of the year with days count of the month.
SELECT
extract(month FROM cal.month) AS month,
(cal.month + interval '1 month') - cal.month AS length
FROM (
SELECT generate_series(
timestamp '2012-01-01',
timestamp '2012-12-01',
interval '1 month'
) AS month
) cal
ORDER BY month;
You can round dates and times with date_trunc().
SELECT
(date_trunc('month', ts.ts) + interval '1 month') -- 1st day of next month.
- date_trunc('day', ts.ts) -- Start of this day.
AS remaining
FROM (SELECT timestamp '2012-02-11 01:00:00' AS ts) ts
Comparisons can be done with <, <=, > and >=.
-- Members that joined in September or later.
SELECT member_id, surname, first_name, join_date
FROM my_schema.member
WHERE join_date >= '2012-09-01'
You can check if dates overlap with OVERLAPS.
SELECT
(DATE '2001-02-16', DATE '2001-12-21')
OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
-- => true