GCP Billing Data Bigquery

Question

I am using the below query to to fetch the billing data from Bigquery. in this i gave a usage_start_time and end time criteria by using milliseconds (UTC). I am using 1 hour difference to query data.with 1 hour difference querying for whole month.

In this method I am getting previous month's invoice data came on next month's 1'st day.Later I found that GCP uses Pacific timezone for billing. What changes needed to get the correct data matching to invoice?. i have to query with 1 hour difference or else more response will come, memory won't be sufficient. As in screenshot with this UTC i am getting July data in August

SELECT  *
        FROM `XXXXXXXXX.XXXXXXXXXX.gcp_billing_export_resource_v1_XXXXXXXXXXXX` 
        WHERE _PARTITIONTIME BETWEEN TIMESTAMP_MILLIS(1690416000000) AND 
            TIMESTAMP_MILLIS(1691020800000) AND cost!=0 AND
            usage_start_time>=TIMESTAMP_MILLIS(1690848000000) AND 
            usage_end_time <=TIMESTAMP_MILLIS(1690851600000)

Answer 1

The way I will approach this is to align the date/ time values in the billing data that is in PST to UTC by converting the time zone prior to query execution.

PST is plus 8 hours from UTC, simply deduct the equivalent milliseconds which is 28800000 to have all date/time data calibrated to UTC, for example, TIMESTAMP_MILLIS(1690848000000 - 28800000). This way when you compare date ranges in your query both datasets will be aligned and return accurate results.

Adding a couple of documents here for your reference:

• Timestamp functions

• Datetime functions