Converting string to Timestamp in BigQuery without timezone

Question

Earlier team had createddate as string and now we are moving it into timestamp column and currently migrating it into timestamp. But running into an issue when converting string to timestamp only with 3 digit milliseconds and without timezone.

2021-04-22 11:00:14.606

I ran the following query and got the results like the below.

select createddate, datetime(createddate),timestamp(createddate) from hd-dev.mm.jira_backlog ;

2021-04-22 11:00:14.606 2021-04-22T11:00:14.606000 2021-04-22 11:00:14.606000 UTC

As you see above, datetime adds T between date and time and also makes 3 digit ms to 6 digit milliseconds, whereas timestamp column adds timezone information along with 6 digit milliseconds. How do I avoid both of them?

Answer 1

You will want to use FORMAT_DATETIME, but it will output a DATETIME, not a TIMESTAMP. See below:

select format_datetime('%Y-%m-%d %H:%M:%E3S', '2021-04-22 11:00:14.606') as new_dt