The standard difference operator (-) returns day-based results for both date, timestamp and timestamp with time zone (the former returns days as int, the latter two return day-based intervals): From the day-based intervals you can extract days with the extract() function: select current_date - '2017-01-01', extract(day from now()::timestamp - '2017-01-01 00:00:00'), extract(day from now() - '2017-01-01 00:00:00Z');

FROM generate_series('2017-03-01'::date, '2017-03-03'::timestamp - interval '1 hour', interval '1 hour' ) AS t(start_ts)