CODEWITHSUNDEEP
amazon-redshift
SUKUMAR S
SUKUMAR S

Reputation: 335

Different Time Stamps in Redshift

Please help me know difference in following SQL statements in Amazon Redshift: -

extract(epoch from
          trunc(convert_timezone('Asia/Calcutta', getDate())
                - 27)
       )::bigint * 1000000

vs

trunc(convert_timezone('Asia/Calcutta',getDate())
      -27)
- INTERVAL '05:30' HOUR TO MINUTE

Please help me with relevant online documents which explain in detail.

Upvotes: 0

Views: 306

Answers (1)

SUKUMAR S
SUKUMAR S

Reputation: 335

Step 1: GETDATE will return TIMESTAMP in UTC, irrespective of where Server is located.

Step 2: convert_timezone converted UTC timestamp into IST.

Step 3: -27 took converted timestamp to 27 days ago.

Step 4: trunc() removed time from timestamp, thus making it date without time in IST.

Step 5: extract converted IST date into epoch seconds.

Step 6: Interval uses datepart and quantity, where abbreviations and plurals of datepart are optional

Upvotes: 1

Related Questions