ruk·si

🐘 PostgreSQL
Date and Time

Updated at 2015-09-26 17:28

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

Sources