1

I currently have a datetime field in the following format: "2019-07-07 15:00:00 UTC". However, this date is an hour and 25 minutes ahead of what it should be. How can I subtract 1 hour and 25 minutes from this time in a new time field?

E.g., "I need 2019-07-07 15:00:00 UTC" to become "2019-07-07 13:35:00 UTC"

Language: SQL Database: Pulling using Bigquery, which pulls from Google Cloud

The attached image shows the part of my SELECT statement I am working with. The first line is the date but in the database string form, the second line converts it to a date, and the third is trying to transform the date to subtract an hour and 25 mins.

Image of error I am getting and select statement referenced.

Thanks in advance for the help!

Jacques
  • 57
  • 2
  • 7
  • What language are you using? What have you tried (and can you share your attempt)? Also, you should probably add the relevant language tag to your question, to get more eyeballs on it. (But the chances are good that "date arithmetic" has already been asked and answered, for whatever language you are using.) – andrewJames Feb 26 '20 at 21:27
  • Yeah I looked around and wasn't seeing anything that worked for this. I am using SQL. I've tried the convert and date add functions here - https://stackoverflow.com/questions/12756769/sql-convert-datetime-and-subtract-hours - but I keep getting a "function cannot be found" error. I have also tried date part like this: datepart(hh,-1.5,starts_at) and that is not working either. – Jacques Feb 26 '20 at 22:08
  • Add these to your question: The database you are using (add it as a tag); the SQL you wrote (add it as formatted code); and the _full text_ of the error messages you get. All that will help people reviewing your question. – andrewJames Feb 26 '20 at 22:27
  • yes I am using Google Bigquery - I added it up above in the original post, but should have commented it for you – Jacques Feb 27 '20 at 14:04

1 Answers1

7

You can try the below for subtracting Hours

SELECT TIMESTAMP_SUB(TIMESTAMP "2019-07-07 15:00:00 UTC", INTERVAL 1 HOUR)

Change your interval accordingly. In your case you can convert it into minutes and then use the MINUTE INTERVAL as shown below

SELECT TIMESTAMP_SUB(starts_at, INTERVAL 85 MINUTE)
Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
  • When one runs the first query on BigQuery gets the following error: `Interval value must be coercible to INT64 type at [1:68]`, because the interval can't be a float. – milia Feb 27 '20 at 15:31
  • Ah! Right..I have updated my answer. Thanks for noticing that @milia – Arun Palanisamy Feb 28 '20 at 04:04