Skip to content
This repository was archived by the owner on Mar 23, 2026. It is now read-only.

Commit 2294c2f

Browse files
obada-abobada-ab
authored
feat: add support for converting interval fields to threeten PeriodDuration (#2838)
Add support for converting BigQuery interval type to threeten PeriodDuration, information about the canonical interval form can be found [here](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#canonical_format_3). Parsing logic was referenced from [here](https://cloud.google.com/bigquery/docs/reference/standard-sql/interval_functions#extract). Fixes #1849 ☕️
1 parent 989d997 commit 2294c2f

4 files changed

Lines changed: 131 additions & 3 deletions

File tree

‎README.md‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -60,13 +60,13 @@ implementation 'com.google.cloud:google-cloud-bigquery'
6060
If you are using Gradle without BOM, add this to your dependencies:
6161

6262
```Groovy
63-
implementation 'com.google.cloud:google-cloud-bigquery:2.31.1'
63+
implementation 'com.google.cloud:google-cloud-bigquery:2.31.2'
6464
```
6565

6666
If you are using SBT, add this to your dependencies:
6767

6868
```Scala
69-
libraryDependencies += "com.google.cloud" % "google-cloud-bigquery" % "2.31.1"
69+
libraryDependencies += "com.google.cloud" % "google-cloud-bigquery" % "2.31.2"
7070
```
7171
<!-- {x-version-update-end} -->
7272

@@ -351,7 +351,7 @@ Java is a registered trademark of Oracle and/or its affiliates.
351351
[kokoro-badge-link-5]: http://storage.googleapis.com/cloud-devrel-public/java/badges/java-bigquery/java11.html
352352
[stability-image]: https://img.shields.io/badge/stability-stable-green
353353
[maven-version-image]: https://img.shields.io/maven-central/v/com.google.cloud/google-cloud-bigquery.svg
354-
[maven-version-link]: https://central.sonatype.com/artifact/com.google.cloud/google-cloud-bigquery/2.31.1
354+
[maven-version-link]: https://central.sonatype.com/artifact/com.google.cloud/google-cloud-bigquery/2.31.2
355355
[authentication]: https://github.com/googleapis/google-cloud-java#authentication
356356
[auth-scopes]: https://developers.google.com/identity/protocols/oauth2/scopes
357357
[predefined-iam-roles]: https://cloud.google.com/iam/docs/understanding-roles#predefined_roles

‎google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/FieldValue.java‎

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,10 +27,16 @@
2727
import java.io.Serializable;
2828
import java.math.BigDecimal;
2929
import java.math.RoundingMode;
30+
import java.time.Duration;
3031
import java.time.Instant;
32+
import java.time.Period;
33+
import java.time.format.DateTimeParseException;
3134
import java.util.List;
3235
import java.util.Map;
3336
import java.util.Objects;
37+
import java.util.regex.Matcher;
38+
import java.util.regex.Pattern;
39+
import org.threeten.extra.PeriodDuration;
3440

3541
/**
3642
* Google BigQuery Table Field Value class. Objects of this class represent values of a BigQuery
@@ -237,6 +243,28 @@ public List<FieldValue> getRepeatedValue() {
237243
return (List<FieldValue>) value;
238244
}
239245

246+
/**
247+
* Returns this field's value as a {@link org.threeten.extra.PeriodDuration}. This method should
248+
* be used if the corresponding field has {@link StandardSQLTypeName#INTERVAL} type, or if it is a
249+
* legal canonical format "[sign]Y-M [sign]D [sign]H:M:S[.F]", e.g. "123-7 -19 0:24:12.000006" or
250+
* ISO 8601.
251+
*
252+
* @throws ClassCastException if the field is not a primitive type
253+
* @throws NullPointerException if {@link #isNull()} returns {@code true}
254+
* @throws IllegalArgumentException if the field cannot be converted to a legal interval
255+
*/
256+
@SuppressWarnings("unchecked")
257+
public PeriodDuration getPeriodDuration() {
258+
checkNotNull(value);
259+
try {
260+
// Try parsing from ISO 8601
261+
return PeriodDuration.parse(getStringValue());
262+
} catch (DateTimeParseException dateTimeParseException) {
263+
// Try parsing from canonical interval format
264+
return parseCanonicalInterval(getStringValue());
265+
}
266+
}
267+
240268
/**
241269
* Returns this field's value as a {@link FieldValueList} instance. This method should only be
242270
* used if the corresponding field has {@link LegacySQLTypeName#RECORD} type (i.e. {@link
@@ -325,4 +353,63 @@ static FieldValue fromPb(Object cellPb, Field recordSchema) {
325353
}
326354
throw new IllegalArgumentException("Unexpected table cell format");
327355
}
356+
357+
/**
358+
* Parse interval in canonical format and create instance of {@code PeriodDuration}.
359+
*
360+
* <p>The parameter {@code interval} should be an interval in the canonical format: "[sign]Y-M
361+
* [sign]D [sign]H:M:S[.F]". More details <a href=
362+
* "https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#canonical_format_3">
363+
* here</a>
364+
*
365+
* @throws IllegalArgumentException if the {@code interval} is not a valid interval
366+
*/
367+
static PeriodDuration parseCanonicalInterval(String interval) throws IllegalArgumentException {
368+
// Pattern is [sign]Y-M [sign]D [sign]H:M:S[.F]
369+
Pattern pattern =
370+
Pattern.compile(
371+
"(?<sign1>[+-])?(?<year>\\d+)-(?<month>\\d+) (?<sign2>[-|+])?(?<day>\\d+) (?<sign3>[-|+])?(?<hours>\\d+):(?<minutes>\\d+):(?<seconds>\\d+)(\\.(?<fraction>\\d+))?");
372+
Matcher matcher = pattern.matcher(interval);
373+
if (!matcher.find()) {
374+
throw new IllegalArgumentException();
375+
}
376+
String sign1 = matcher.group("sign1");
377+
String year = matcher.group("year");
378+
String month = matcher.group("month");
379+
String sign2 = matcher.group("sign2");
380+
String day = matcher.group("day");
381+
String sign3 = matcher.group("sign3");
382+
String hours = matcher.group("hours");
383+
String minutes = matcher.group("minutes");
384+
String seconds = matcher.group("seconds");
385+
String fraction = matcher.group("fraction");
386+
387+
int yearInt = Integer.parseInt(year);
388+
int monthInt = Integer.parseInt(month);
389+
if (Objects.equals(sign1, "-")) {
390+
yearInt *= -1;
391+
monthInt *= -1;
392+
}
393+
394+
int dayInt = Integer.parseInt(day);
395+
if (Objects.equals(sign2, "-")) {
396+
dayInt *= -1;
397+
}
398+
if (sign3 == null) {
399+
sign3 = "";
400+
}
401+
402+
String durationString =
403+
sign3
404+
+ "PT"
405+
+ hours
406+
+ "H"
407+
+ minutes
408+
+ "M"
409+
+ seconds
410+
+ (fraction == null ? "" : "." + fraction)
411+
+ "S";
412+
413+
return PeriodDuration.of(Period.of(yearInt, monthInt, dayInt), Duration.parse(durationString));
414+
}
328415
}

‎google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/FieldValueTest.java‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,8 +27,13 @@
2727
import com.google.common.collect.ImmutableMap;
2828
import com.google.common.io.BaseEncoding;
2929
import java.math.BigDecimal;
30+
import java.time.Duration;
31+
import java.time.Period;
32+
import java.util.LinkedHashMap;
3033
import java.util.Map;
34+
import java.util.Map.Entry;
3135
import org.junit.Test;
36+
import org.threeten.extra.PeriodDuration;
3237

3338
public class FieldValueTest {
3439

@@ -43,6 +48,10 @@ public class FieldValueTest {
4348
ImmutableMap.of("v", "123456789.123456789");
4449
private static final Map<String, String> STRING_FIELD = ImmutableMap.of("v", "string");
4550
private static final Map<String, String> TIMESTAMP_FIELD = ImmutableMap.of("v", "42");
51+
private static final Map<String, String> INTERVAL_FIELD_1 =
52+
ImmutableMap.of("v", "P3Y2M1DT12H34M56.789S");
53+
private static final Map<String, String> INTERVAL_FIELD_2 =
54+
ImmutableMap.of("v", "3-2 1 12:34:56.789");
4655
private static final Map<String, String> BYTES_FIELD = ImmutableMap.of("v", BYTES_BASE64);
4756
private static final Map<String, String> NULL_FIELD =
4857
ImmutableMap.of("v", Data.nullOf(String.class));
@@ -74,6 +83,17 @@ public void testFromPb() {
7483
value = FieldValue.fromPb(TIMESTAMP_FIELD);
7584
assertEquals(FieldValue.Attribute.PRIMITIVE, value.getAttribute());
7685
assertEquals(42000000, value.getTimestampValue());
86+
value = FieldValue.fromPb(INTERVAL_FIELD_1);
87+
assertEquals(FieldValue.Attribute.PRIMITIVE, value.getAttribute());
88+
PeriodDuration periodDuration =
89+
PeriodDuration.of(Period.of(3, 2, 1), Duration.parse("PT12H34M56.789S"));
90+
assertEquals(periodDuration, value.getPeriodDuration());
91+
assertEquals("P3Y2M1DT12H34M56.789S", value.getStringValue());
92+
value = FieldValue.fromPb(INTERVAL_FIELD_2);
93+
assertEquals(FieldValue.Attribute.PRIMITIVE, value.getAttribute());
94+
periodDuration = PeriodDuration.of(Period.of(3, 2, 1), Duration.parse("PT12H34M56.789S"));
95+
assertEquals(periodDuration, value.getPeriodDuration());
96+
assertEquals("3-2 1 12:34:56.789", value.getStringValue());
7797
value = FieldValue.fromPb(BYTES_FIELD);
7898
assertEquals(FieldValue.Attribute.PRIMITIVE, value.getAttribute());
7999
assertArrayEquals(BYTES, value.getBytesValue());
@@ -146,4 +166,22 @@ public void testEquals() {
146166
assertEquals(recordValue, FieldValue.fromPb(RECORD_FIELD));
147167
assertEquals(recordValue.hashCode(), FieldValue.fromPb(RECORD_FIELD).hashCode());
148168
}
169+
170+
@Test
171+
public void testParseCanonicalInterval() {
172+
Map<String, PeriodDuration> intervalToPeriodDuration = new LinkedHashMap<>();
173+
intervalToPeriodDuration.put(
174+
"125-7 -19 -0:24:12.001", PeriodDuration.parse("P125Y7M-19DT0H-24M-12.001S"));
175+
intervalToPeriodDuration.put("-15-6 23 23:14:05", PeriodDuration.parse("P-15Y-6M23DT23H14M5S"));
176+
intervalToPeriodDuration.put(
177+
"06-01 06 01:01:00.123456", PeriodDuration.parse("P6Y1M6DT1H1M0.123456S"));
178+
intervalToPeriodDuration.put("-0-0 -0 -0:0:0", PeriodDuration.parse("P0Y0M0DT0H0M0S"));
179+
intervalToPeriodDuration.put(
180+
"-99999-99999 9999 999:999:999.999999999",
181+
PeriodDuration.parse("P-99999Y-99999M9999DT999H999M999.999999999S"));
182+
for (Entry<String, PeriodDuration> entry : intervalToPeriodDuration.entrySet()) {
183+
assertEquals(FieldValue.parseCanonicalInterval(entry.getKey()), entry.getValue());
184+
System.out.println(FieldValue.parseCanonicalInterval(entry.getKey()));
185+
}
186+
}
149187
}

‎google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/it/ITBigQueryTest.java‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1228,8 +1228,11 @@ public void testIntervalType() throws InterruptedException {
12281228
.build();
12291229
TableResult result = bigquery.query(queryJobConfiguration);
12301230
assertNotNull(result.getJobId());
1231+
PeriodDuration periodDuration =
1232+
PeriodDuration.of(Period.of(125, 7, -19), java.time.Duration.parse("PT24M12.000006S"));
12311233
for (FieldValueList values : result.iterateAll()) {
12321234
assertEquals("125-7 -19 0:24:12.000006", values.get(0).getValue());
1235+
assertEquals(periodDuration, values.get(0).getPeriodDuration());
12331236
}
12341237
} finally {
12351238
assertTrue(bigquery.delete(tableId));

0 commit comments

Comments
 (0)