Google bigquery date manipulation

1
votes

I'm trying to run a query in google bigquery where I subtract the hour from a certain time. For some reason I'm getting an error "DATE_SUB does not support the HOUR date part" with the line of code below: (embedded in a bigger sql statement)

DATE_SUB(t2.time, INTERVAL -1 HOUR)

I also tried DATE_ADD like this:

DATE_ADD(t2.click_time, -1, 'hour')

but I get the error "expected INTERVAL expression at ..."

Does anyone have any suggestions on how to address this?

I'm using standard SQL (use legacy sql is unchecked).

Thanks!

1
google-bigquery

1 Answers

2
votes

you should use DATETIME_SUB() instead

#standardSQL
SELECT 
  CURRENT_DATETIME(), 
  DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 HOUR)

if you have your field as a TIMESTAMP - use TIMESTAMP_SUB()

#standardSQL
SELECT 
  CURRENT_TIMESTAMP(), 
  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)