Prepend an hash (
#) to table name to create a temporary table.
CREATE TABLE #mytable AS SELECT 1;
#mytable will be clean up when session will be closed.
Two simultanei sessions can create a temporary table with the same name
with no conflicts.
I use an integer field to store dates in the
YYYYMMDD format. It is really
handy cause it can be sorted naturally, even in other contexts like folder
or file naming conventions. It is true that first two digits
20xxxxxx are redundant
but they make it really easy to humans to recognize it is a date, like
20170928 the day this article was published. So I use this format in
many other databases, it crosses contexts easily and in this section I
will write the snippets I use to convert it or manipulate it in Redshift,
YYYY-MM-DDstring format that has the same benefits and has also the advantage to be easier to cast in Redshift, for instance
SELECT SYSDATE::DATEreturns current timestamp in this format.
Convert timestamp to yyyymmdd
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'), 99999999); SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')::INTEGER;
First day of last week
SELECT TO_CHAR(DATE_ADD('day', -7, DATE_TRUNC('week', SYSDATE)), 'YYYYMMDD');