BigQuery - Sorting a Datetime string

Question

I have a column of datatype String. Eg: 2025-01-20T23:38:31.8223598Z

If I apply a ORDER BY on this column inside a window function as below:

ROW_NUMBER() OVER (PARTITION BY id ORDER BY modifiedOn DESC) AS rank

Will the sorting be actually based on the DateTime or as a String. Do I need to explicitly convert this to a DateTime/Timestamp before ordering.

Any insights on this please.

If you don't cast , it's going to sort by however it's stored. Assuming your data is always in that format, it will sort the same. The real question is, why are you storing a timestamp as a string? — Andrew
– Andrew, Commented Feb 7 at 18:50
Surely you could just try it and see? You should always store your data using the correct datatype, as using functions to convert it can result in poor performance, and/or issues and exceptions if you ever have a row with invalid data. — Dale K
– Dale K, Commented Feb 8 at 3:37

Fred Alisson · Accepted Answer · 2025-02-07 18:55:06Z

1

If your column has all the values in the format like your example (YYYY-MM-DDTHH:MM:SS...), it will be chronological sorted right. It's because each part of the datetime is already ordered by itself, a string with year 2020 come first than a string with year 2022. So is based on the largest to smallest unit even if it's string.

I do recommend you to do it explicity and convert, because of data quality, consistency, optimization, etc. You could use PARSE_TIMESTAMP:

ROW_NUMBER() OVER (
  PARTITION BY id 
  ORDER BY PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', modifiedOn) DESC
) AS rank

Let me know if you need more details or have any doubt!

Resources: https://www.ionos.com/digitalguide/websites/web-development/iso-8601/, https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#parse_timestamp

answered Feb 7 at 18:55

Fred Alisson

1945 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

BigQuery - Sorting a Datetime string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related