You are working with a Snowflake table named 'transactions' that contains a 'transaction_time' column of data type 'TIMESTAMP NTZ'. You need to retrieve all transactions that occurred within the last

Question

Options

• ASELECT FROM transactions WHERE transaction_time DATEADD(hour, -24,
• BSELECT FROM transactions WHERE transaction_time - INTERVAL '24 hours'
• CSELECT FROM transactions WHERE transaction_time BETWEEN - INTERVAL '24 hours' AND
• DSELECT FROM transactions WHERE transaction_time CONVERT TIMEZONE('UTC',
• ESELECT FROM transactions WHERE transaction_time SYSDATE() - (24/24);

• A
  4% (1)
• B
  44% (12)
• C
  15% (4)
• D
  30% (8)
• E
  7% (2)

Explanation

Option B correctly uses CURRENT_TIMESTAMP() - INTERVAL '24 hours' in a simple >= comparison, which is the idiomatic, SARGable Snowflake approach - it applies no transformation to the indexed transaction_time column itself, allowing Snowflake's micro-partition pruning to work efficiently on a TIMESTAMP NTZ column.

Option A's DATEADD syntax as written is malformed (missing the reference timestamp argument), making it invalid. Option C's BETWEEN form double-evaluates CURRENT_TIMESTAMP() and, as written in the choices, is syntactically incomplete. Option D introduces CONVERT_TIMEZONE which is not only syntactically broken here but semantically wrong - TIMESTAMP NTZ stores no timezone, so timezone conversion is both unnecessary and adds overhead that defeats pruning. Option E uses SYSDATE(), which in Snowflake returns a DATE (not TIMESTAMP), causing an implicit type cast against a TIMESTAMP NTZ column and making the arithmetic 24/24 = 1 day semantically ambiguous and inefficient.

Memory tip: Anchor on "NTZ = No Timezone = No CONVERT_TIMEZONE." For time-window filters in Snowflake, reach for >= CURRENT_TIMESTAMP() - INTERVAL 'N hours' - it keeps the column untouched, letting micro-partition pruning do its job.

No community discussion yet for this question.