[BigQuery] Regarding Conversion String to Date Type Issue

Google Cloud Data Analytics
1

Hi there ,

I have column which contains timestamp and it is String Datatype

I want cast has Date Data type

Used PARSE_TIMESTAMP Function but , it don’t work

PARSE_TIMESTAMP(‘%Y-%m-%dT%H:%M:%E*S%Z’, ’ 2024-06-22T19:00:01+00.000Z’)

date format example : ’ 2024-06-22T19:00:01+00.000Z’

Can anyone advice me on this scenerio

3 Likes
2

The PARSE_TIMESTAMP function expects a string that adheres to a specific format. Your timestamp string (‘2024-06-22T19:00:01+00.000Z’) includes milliseconds and timezone information, which the format string should handle.

While PARSE_TIMESTAMP handles timestamps (date and time), you want to extract just the date portion.

Direct Conversion to Date:

This is the most straightforward approach if you only need the date:

SELECT PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%Ez', '2024-06-22T19:00:01+00.000Z') AS timestamp,
       DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%Ez', '2024-06-22T19:00:01+00.000Z')) AS date;

Handling Timezone Information:

This approach converts the timestamp string to UTC before converting it to date format. Since the example already includes timezone information, this is handled by the format string.

SELECT PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%Ez', '2024-06-22T19:00:01+00.000Z') AS timestamp_utc,
       DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%Ez', '2024-06-22T19:00:01+00.000Z')) AS date_utc;

Explanation:

  • Format String: We modify the format string to:
    • %E*S: This matches the optional fractional seconds (milliseconds).
    • %Ez: This matches the timezone offset, including ‘Z’ at the end (e.g., ‘+00:00’ or ‘Z’ for UTC).

Important Considerations:

  • Time Zones: If your timestamp strings have different time zones, be sure to adjust them to a consistent zone (e.g., UTC) before converting to dates. You can use the TIMESTAMP function for this.
  • Data Validation: Ensure your timestamp strings are consistently formatted. Unexpected variations can lead to errors.

Applying to Your Column:

Here’s how you’d apply this to your BigQuery column:

SELECT DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%Ez', your_timestamp_column)) AS date_column
FROM your_table;

Example Output:

timestamp_utc          date_utc
2024-06-22 19:00:01.000000+00:00   2024-06-22
2 Likes
3

Hey @ms4446

The parse_timestamp doesn’t work

because when I query it

SELECT PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%Ez', '2024-06-22T19:00:01+00.000Z') AS timestamp,
       DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%Ez', '2024-06-22T19:00:01+00.000Z')) AS date;

I getting this error

Failed to parse input string “2024-06-22T19:00:01+00.000Z”

Still timestamp format is not matching

Do you have any idea about it

1 Like
4

The error you’re encountering indicates that the format string doesn’t correctly match the timestamp format. Let’s refine the format string further. Given your timestamp format: ‘2024-06-22T19:00:01+00.000Z’ Here’s the correct approach:

%Y-%m-%d: Date part T%H:%M:%S: Time part %Ez: Timezone part (including milliseconds) Let’s adjust the format string:

SELECT PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%S%E*1S%Ez', '2024-06-22T19:00:01+00.000Z') AS timestamp,
       DATE(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%S%E*1S%Ez', '2024-06-22T19:00:01+00.000Z')) AS date;

Explanation:

  • %Y-%m-%d: Year, month, day
  • T%H:%M:%S: Hours, minutes, seconds
  • %E*1S: Optional fractional seconds (milliseconds) with one or more digits
  • %Ez: Timezone offset

Alternatively, if the above does not work, you can handle the timestamp using string manipulation functions to ensure it’s parsed correctly. Here’s a different approach:

SELECT TIMESTAMP('2024-06-22T19:00:01+00.000Z') AS timestamp,
       DATE(TIMESTAMP('2024-06-22T19:00:01+00.000Z')) AS date;

If you want to apply this to a column in your table:

SELECT TIMESTAMP(your_timestamp_column) AS timestamp_column,
       DATE(TIMESTAMP(your_timestamp_column)) AS date_column
FROM your_table;

This approach leverages the TIMESTAMP function directly, which is capable of handling ISO 8601 format timestamps including timezone and milliseconds. This simplifies the conversion without needing a complex format string.

1 Like
5

Hey @ms4446

The format what you suggest is not working.

for this example

2024-06-22T19:00:01+00.000Z

Even timestamp function is not working

SELECT TIMESTAMP('2024-06-22T19:00:01+00.000Z') AS timestamp

i think timestamp format is not support BigQuery.

So i used regex replace to change the timestamp format by removing +00.0

2024-06-22T19:00:01+00.000Z ---->  2024-06-22T19:00:01.0Z

Thank you

2 Likes
6

Hello ms4446,

I have having an issue with extracting date out of the string timestamp column with sample value which looks like ‘Jul 23, 2024 12:00:00 AM’.

Just want to extract the date part which should look like ‘23-07-2024’.

Can you help..

7

To extract the date part from a string like ‘Jul 23, 2024 12:00:00 AM’ in Google Cloud BigQuery and format it as ‘23-07-2024’, you can use PARSE_DATETIME to convert the string into a DATETIME and then format the result using FORMAT_DATETIME.

Here’s a step-by-step approach:

Solution:

SELECT FORMAT_DATETIME('%d-%m-%Y', PARSE_DATETIME('%b %d, %Y %I:%M:%S %p', 'Jul 23, 2024 12:00:00 AM')) AS formatted_date;

Applying to a column:

If you have a column in your table that contains similar timestamp strings, you can apply the same approach:

SELECT FORMAT_DATETIME('%d-%m-%Y', PARSE_DATETIME('%b %d, %Y %I:%M:%S %p', your_timestamp_column)) AS formatted_date
FROM your_table;
1 Like