How to Query Date and Time in Redshift

Redshift has the following functions/methods to get the current date and time:

select now();  -- date and time in string datatype
select sysdate; -- date and time in timestamp datatype
select current_date; -- date in date format
select current_time; -- time in timestamp format

Note the difference between now() and the other methods with regard to the datatype of the returned value. This means you must use sysdate() or current_date when doing date and time manipulation.

To find rows between two dates or timestamps:

FROM events
where event_date between '2018-01-01' and '2018-01-31';

-- Can include time by specifying in YYYY-MM-DD hh:mm:ss format
-- Can also use the methods above except for now()
FROM events
WHERE event_date BETWEEN '2018-01-01 12:00:00' AND current_date;

To find rows created within the last week:

FROM events
WHERE event_date > dateadd(week, -1, sysdate);

The dateadd() function accepts second, minute, hour, day, week, month, and year. To get the complete list of possible values, please refer to the Redshift Documentation.

You can extract part of a timestamp by applying the EXTRACT() or DATE_PART() functions:

select extract(minute from sysdate);  -- hour, day, month, year, century
select date_part(minute, sysdate);  -- hour, day, month, year, century

-- returns 0-6 (integer), where 0 is Sunday and 6 is Saturday
SELECT extract(dow from sysdate);
SELECT extract(dow, sysdate);

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

For all possible values to use with EXTRACT() and DATE_PART() please refer to the Redshift Documentation.

To convert a timestamp to a unix timestamp (integer seconds):

-- This assumes midnight
SELECT date_part(epoch, '2018-09-20');

-- You can specify an exact timestamp to be converted down to the second
SELECT date_part(epoch, '2018-12-09 14:53:21');

-- calling unix_timestamp without a parameter will be like calling it for current timestamp
SELECT date_part(epoch, sysdate);

To calculate the difference between two timestamps, you can use the DATEDIFF() command:

-- show weeks between two dates
select datediff(week, '2018-01-01', '2018-12-31') as numweeks;

-- show hours between since a specific date
select datediff(hour, '2018-12-01', sysdate) as numhours;

DATEDIFF() accepts the same values as EXTRACT() and DATE_PART() for its first parameter (eg week, day, hour, etc).