🐘 PostgreSQL -
Date and Time

Updated at 2015-09-26 17:28

Prefer 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.

CREATE TABLE my_table (
  my_table_id  serial PRIMARY KEY,
  created_at   timestamp DEFAULT clock_timestamp()
);

For recording future events, consider having three columns:

UTC timestamp to answer e.g. "get events in the next hour" or "sort by date"
timestamp without time zone as the ground truth for original intent; when user selects an event time, they mean "local time", whatever local time that is and timezones can change with a little notice
precise location for the event (where it will be held) from which you can get the timezone. Or precise timezone if there is no location. Handle time zones on your application layer.

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

🐘 PostgreSQL - Date and Time

Sources

🐘 PostgreSQL -
Date and Time