My company started using BigQuery for our data warehouse a few months ago so I'm relatively new to BigQuery. I was working on building a dataset of sales statistics and item availability over a time period and I discovered something that I couldn't explain and wanted to see if there was a reason why I'm seeing this so I can understand this better.
Below is some example code. I'm calculating the length in seconds of a day. The why isn't important. I noticed that when viewing the results that the length of 3/12/2023 in seconds per the TimeStamp_Diff() function is 82,800 seconds and 11/5/2023 is 90,000 seconds and not the expected 86,400 seconds that a day would be. Both are off by 3,600 seconds in opposite directions.
I'm going to do additional testing to see if this changes per year.
Is anyone willing to explain why this is the case when using this function?
Maybe there's a fundamental flaw in my understanding? I'm thinking 60 seconds x 60 minutes x 24 hours should equal 86,400 seconds per day but for some reason these two specific days are different.
--Replace [dataset] when testing in your environment
--Create a table to store dates
CREATE TABLE [dataset].days (
Calendar_Date DATE);
--Declare Variables
DECLARE Year INT64;
DECLARE Start_Date DATE;
DECLARE End_Date DATE;
--Set Variables
SET Year = 2023; --testing with other years to see if this is consistent in other years.
SET Start_Date = DATE(Year, 1, 1);
SET End_Date = DATE_ADD(Start_Date, INTERVAL 1 Year);
--Loop through days in a year
WHILE Start_Date < End_Date
DO
INSERT INTO [dataset].days VALUES(Start_Date);
SET Start_Date = DATE_ADD(Start_Date, INTERVAL 1 DAY);
END WHILE;
SELECT Calendar_Date Start_Date,
DATE_ADD(Calendar_Date, INTERVAL 1 DAY) Next_Date,
TIMESTAMP(Calendar_Date, "America/New_York") Start_Date_TimeStamp,
TIMESTAMP(DATE_ADD(Calendar_Date, INTERVAL 1 DAY), "America/New_York") Next_Date_TimeStamp,
TIMESTAMP_DIFF(TIMESTAMP(Calendar_Date, "America/New_York"), TIMESTAMP(DATE_ADD(Calendar_Date, INTERVAL 1 DAY), "America/New_York"), SECOND) Length_Of_Day_Seconds
FROM [dataset].days
LinkedIn
Twitter
