PostgreSQL

How to Query Date and Time in PostgreSQL

Get the date and time time right now:

select now(); -- date and time
select current_date; -- date
select current_time; -- time

Find rows between two absolute timestamps:

select count(1)
from events
where time between '2018-01-01' and '2018-01-31'

Find rows created within the last week:

select count(1)
from events
where time > now() - interval '1 week'; -- or '1 week'::interval, as you like

Find rows created between one and two weeks ago:

select count(1)
from events
where time between (now() - '1 week'::interval) and (now() - '2 weeks'::interval);

Extracting part of a timestamp:

select date_part('minute', now()); -- or hour, day, month

Get the day of the week from a timestamp:

-- returns 0-6 (integer), where 0 is Sunday and 6 is Saturday
select date_part('dow', now());

-- returns a string like monday, tuesday, etc
select to_char(now(), 'day');

Converting a timestamp to a unix timestamp (integer seconds):

select date_part('epoch', now());

Calculate the difference between two timesetamps:

-- Difference in seconds
select date_part('epoch', delivered_at) - date_part('epoch', shipped_at); -- or minute, hour, week, day, etc

-- Alternatively, you can do this with `extract`
select extract(epoch from delivered_at) - extract(epoch from shipped_at);