CODEWITHSUNDEEP
sqlgoogle-bigquery
Nik S
Nik S

Reputation: 105

Format date in db correctly

Hi I'm using Bigquery and I have a large table with the dates inserted in the format dd/mm/yyyy hh:mm

I want to filter this field by date range (i.e. dates for the month of October) but because of the formatting I can't seem to get it to work. Any help would be appreciated!

Thanks,

Nik

Upvotes: 1

Views: 63

Answers (1)

aphid
aphid

Reputation: 1163

You would make the data easier to work with by manually converting to Bigquery's native timestamp type. 

SELECT r1, TIMESTAMP(CONCAT(SUBSTR(r1, 6, 4),"-",
SUBSTR(r1, 3, 2), "-", SUBSTR(r1, 0, 2), SUBSTR(r1, 10), ":00")) AS `time` 
FROM `myTable`  WHERE <insert your conditions here>;

Note that this kind of query is quite slow, because the entire table will need to be scanned. You can make this query much faster by inserting the data from the conversion as another row in the table (e.g. using timestamps in this other row). If you do this the database will be able to use its native indexes to do your comparisons which would speed up most queries.

Upvotes: 1

Related Questions