How to extract date and time from Big Query

I've been using the follwing query to extract date from a data source, but I'd like to include the time as well. What would be the final correct query to also include time along with the date?

SELECT sequence,orderId,status,idSku,SKU,invoiceNumbers,TrackingNumber,InvoiceKey,deliveredDate,finished,
EXTRACT (date from CreationDateBR) as data,
FROM 'xxxxxxxxxxxxxx'
WHERE creationDateBR BETWEEN '2021-06-25' AND '2021-07-28'
AND yyy IN ('xxxxxxxxxxxx')
AND zzzzz IN ('xxxxxxx')
ORDER BY status,creationDateBR

1 answer

  • answered 2021-07-27 18:33 CaioT

    You didn't provide any sample data but assuming the column 'CreationDateBR' is a regular timestamp data type you can simply pass datetime instead of date:

    SELECT sequence,orderId,status,idSku,SKU,invoiceNumbers,TrackingNumber,InvoiceKey,deliveredDate,finished,
    EXTRACT (datetime from CreationDateBR) as data,
    FROM 'xxxxxxxxxxxxxx'
    WHERE creationDateBR BETWEEN '2021-06-25' AND '2021-07-28'
    AND yyy IN ('xxxxxxxxxxxx')
    AND zzzzz IN ('xxxxxxx')
    ORDER BY status,creationDateBR
    

    I ran a quick example here and here are the results:

    SELECT EXTRACT (date from timestamp_field_0) as date,
    FROM `test_ds.table1`
    

    Output: 2021-07-22

    SELECT EXTRACT (datetime from timestamp_field_0) as date,
    FROM `test_ds.table1`
    

    Output: 2021-07-22T00:00:00

    SELECT EXTRACT (time from timestamp_field_0) as date,
    FROM `test_ds.table1`
    

    Output: 00:00:00

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum