When working with time-series data in Google BigQuery, a common hurdle is sorting 12-hour time strings formatted with AM/PM indicators (such as "01:00:00 PM"). If you try to sort them directly as text, or attempt to use standard casting, you will likely encounter unexpected sorting orders or runtime errors.

Why Does BigQuery Throw "Invalid time string"?

When you attempt to run a query like CAST(ActivityTime AS TIME) on a string like "12:00:00 AM", BigQuery throws an Invalid time string error.

This happens because BigQuery's CAST() function expects a strict ISO 8601 formatted 24-hour time string (e.g., "13:00:00"). It does not natively recognize 12-hour clock formats or trailing AM/PM designations during standard type casting.

Furthermore, sorting the raw string without casting leads to alphabetical ordering rather than chronological ordering. Under string sorting, "01:00:00 AM" comes right before "01:00:00 PM", followed by "02:00:00 AM", which interleaves morning and evening hours incorrectly.

The Solution: Use PARSE_TIME()

To convert a 12-hour AM/PM string into a valid BigQuery TIME object, you should use the PARSE_TIME() function. This function allows you to specify a format string matching your exact text pattern.

Option 1: Using the shorthand %r format specifier

The format token %r represents a 12-hour time notation (equivalent to %I:%M:%S %p).

SELECT *
FROM `modern-ellipse-438015-c0.Bellabeat.HourlyCalories3-4` 
ORDER BY 
  Id, 
  ActivityDate, 
  PARSE_TIME('%r', ActivityTime) ASC;

Option 2: Explicitly defining format components

If your string format varies slightly (e.g., leading zeros or single-digit hours), you can explicitly write out the format specification:

  • %I: Hour (12-hour clock) as a decimal number (01-12).
  • %M: Minute as a decimal number (00-59).
  • %S: Second as a decimal number (00-59).
  • %p: AM or PM indicator.
SELECT *
FROM `modern-ellipse-438015-c0.Bellabeat.HourlyCalories3-4` 
ORDER BY 
  Id, 
  ActivityDate, 
  PARSE_TIME('%I:%M:%S %p', ActivityTime) ASC;

Pro Tip: Parsing Combined Date and Time

If your ActivityDate column is also stored as a string, ordering by ActivityDate first and then ActivityTime might still cause issues if the date string isn't in YYYY-MM-DD format. To ensure accurate chronological sorting across multiple days, combine both columns into a single DATETIME using PARSE_DATETIME():

SELECT *, 
  PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', CONCAT(ActivityDate, ' ', ActivityTime)) AS FullDateTime
FROM `modern-ellipse-438015-c0.Bellabeat.HourlyCalories3-4` 
ORDER BY Id, FullDateTime ASC;

Summary

When dealing with custom date and time formats in BigQuery, avoid using CAST(). Instead, rely on string parsing functions like PARSE_TIME(), PARSE_DATE(), or PARSE_DATETIME() using format parameters like %r or %I:%M:%S %p to correctly convert strings into real temporal types for sorting.