Error when converting to BigQuery timestamp from ISO 8601 date string

Question

I have an ISO 8601 date string in the following format:

String myIsoDateString = "2019-02-27T23:00:00.000Z"

I need to use the date string as part of a query I'm running in BigQuery. I'm trying to use the com.google.cloud.bigquery.QueryParameterValue class to convert it to a QueryParameterValue, with a type of timestamp, like this:

QueryParameterValue.timestamp(myIsoDateString)

This gives me an error:

java.lang.IllegalArgumentException: Invalid format: "2019-02-27T23:00:00.000Z" is malformed at "T23:00:00.000Z"

The inline help in Eclipse for the timestamp method states that it:

Creates a QueryParameterValue object with a type of TIMESTAMP. Must be in the format"yyyy-MM-dd HH:mm:ss.SSSSSSZZ", e.g. "2014-08-19 12:41:35.220000+00:00".

How do I convert myIsoDateString to the required format? Is there a better method I can use that will handle converting from an ISO 8601 string to a timestamp in BigQuery?

Innocency

Substitute the T with a space? – Tweeze

Answer 1

This works:

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;

Convert myIsoDateString to microseconds and store it as a long:

long myDateInMicroseconds = DateTime.parse(myIsoDateString).toDateTime(DateTimeZone.UTC).getMillis() * 1000;

Then pass it as the argument to QueryParameterValue.timestamp:

QueryParameterValue.timestamp(myDateInMicroseconds)

Innocency

Thanks for supplying the solution you found. I’d probably prefer the more explicative (but also longer) TimeUnit.MILLISECONDS.toMicros(milliSecondValue). Which in turn still loses the microsecond precision from the string (had it had microsecond precision). The perfect conversion exists, but would be still a bit longer. – Tweeze

@OleV.V. yes - I've updated the answer to show this, thanks. If you edit your answer a bit more to remove the initial assumption about milliseconds, I can mark it as the answer (I don't mind you borrowing on my solution, as long as there's a good and complete answer to the question :) – Innocency

Thanks. Chris, that’s kind. I think you should accept your own answer as being most helpful to solve your own problem. In any case you are correct, there was nothing helpful about my wrong guess, so I have taken it out of my answer. – Tweeze

Answer 2

To anyone who is just trying to parse ISO 8601 in BigQuery (this post is the first Google result), try this:

SELECT
PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ',  '2018-10-12T13:22:27.120Z')

Moises

Thanks! This should be on Documentation page! – Ermeena

Agreed on having this in the doc. If anyone else wants to throw some feedback on this page, together, we can achieve more :D cloud.google.com/bigquery/docs/reference/standard-sql/… – Tactile

Although not what the OP asked, if you don't have the Z part, try this: SELECT PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S', '2022-11-27T00:18:13.081185') – Emunctory

Answer 3

I would expect the answer the answer by Felipe Hoffa to work and give you what you want: replace the T with a space. Here are a couple of other suggestions.

Be explicit: While the documentation gives a quite clear example of an expected string, it’s not clear from the error message nor from the documentation (link below, but you’ve got it in Eclipse already) whether QueryParameterValue.timestamp accepts the Z as an offset. If we want to be sure and also be more explicit about supplying the format asked for (always nice for those managing your code after you):

    DateTimeFormatter timestampFormatter
            = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSSSSSxxx");
    String myIsoDateString = "2019-02-27T23:00:00.000Z";
    OffsetDateTime dateTime = OffsetDateTime.parse(myIsoDateString);
    QueryParameterValue.timestamp(dateTime.format(timestampFormatter));

This passes the string 2019-02-27 23:00:00.000000+00:00 to timestamp.

Pass a long: timestamp comes in an overloaded version taking a Long argument. Again it’s not documented what the long value should be. Edit: Your own answer revealed that timestamp(Long) requires microseconds since the epoch. The fully correct way to convert without any risk of unnecessary loss of precision is:

    String myIsoDateString = "2019-02-27T23:00:00.000Z";
    Instant dateTime = Instant.parse(myIsoDateString);
    long microsecondsSinceEpoch = TimeUnit.SECONDS.toMicros(dateTime.getEpochSecond())
            + TimeUnit.NANOSECONDS.toMicros(dateTime.getNano());
    QueryParameterValue.timestamp(microsecondsSinceEpoch);

A value of 1 551 308 400 000 000 is passed to timestamp.

Disclaimer: I’m not a BigQuery user.

Links

• QueryParameterValue documentation
• Oracle tutorial: Date Time

Tweeze

Thanks @Ole V.V. I found that it's microseconds since the epoch that needs to be passed if a long is being passed - see my answer – Innocency

Answer 4

The error says this is not the right format:

 "2019-02-27T23:00:00.000Z"

But this one would be:

 "yyyy-MM-dd HH:mm:ss.SSSSSSZZ"

Try replacing the T between date and time with a .

Dyspeptic

Answer 5

ISO 8601 versus SQL

The ISO 8601 standard uses a T in the middle to separate the year-month-day portion from the hour-minute-second portion.

The SQL style uses a SPACE in the middle rather than a T.

Replace.

String input = "2019-02-27T23:00:00.000Z".replace( "T" , " " ) ;
QueryParameterValue.timestamp( input ) ;

Ditto for going the other direction. The java.time classes use the ISO 8601 formats by default when parsing/generating text.

String input = "2019-02-27 23:00:00.000Z".replace( " " , "T" ) ;
Instant instant = Instant.parse( input ) ;
ZonedDateTime zdt = instant.atZone( ZoneId.of( "Pacific/Auckland" ) ) ;

Somatic

Answer 6

In case someone is having problems figuring it out for the format:

2023-01-01T00:00:00.000+01:00

I used this method to parse the string (according with these options):

PARSE_TIMESTAMP('%FT%R:%E3S%Ez', '2023-01-01T00:00:00.000+01:00')

Agripinaagrippa