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
current_date when doing date and time manipulation.
To find rows between two dates or timestamps:
SELECT * 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() SELECT * FROM events WHERE event_date BETWEEN '2018-01-01 12:00:00' AND current_date;
To find rows created within the last week:
SELECT * FROM events WHERE event_date > dateadd(week, -1, sysdate);
dateadd() function accepts
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
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
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
-- 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
DATE_PART() for its first parameter (eg