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

Commit fd3751a

Browse files
stephaniewang526gcf-owl-bot[bot]
stephaniewang526
and
gcf-owl-bot[bot]
authored
feat: add Interval type support (#1844)
* feat: add Interval type support Fixes b/208051516 * 🦉 Updates from OwlBot See https://github.com/googleapis/repo-automation-bots/blob/main/packages/owl-bot/README.md * add threeten-extra PeriodDuration support * add unit test coverage Co-authored-by: Owl Bot <gcf-owl-bot[bot]@users.noreply.github.com>
1 parent 7ffe963 commit fd3751a

7 files changed

Lines changed: 131 additions & 3 deletions

File tree

‎google-cloud-bigquery/pom.xml‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,10 @@
8585
<groupId>com.google.code.gson</groupId>
8686
<artifactId>gson</artifactId>
8787
</dependency>
88+
<dependency>
89+
<groupId>org.threeten</groupId>
90+
<artifactId>threeten-extra</artifactId>
91+
</dependency>
8892

8993
<!-- auto-value creates a class that uses an annotation from error_prone_annotations -->
9094
<dependency>

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,9 @@ public LegacySQLTypeName apply(String constant) {
9696
/** Represents JSON data */
9797
public static final LegacySQLTypeName JSON =
9898
type.createAndRegister("JSON").setStandardType(StandardSQLTypeName.JSON);
99+
/** Represents duration or amount of time. */
100+
public static final LegacySQLTypeName INTERVAL =
101+
type.createAndRegister("INTERVAL").setStandardType(StandardSQLTypeName.INTERVAL);
99102

100103
private static Map<StandardSQLTypeName, LegacySQLTypeName> standardToLegacyMap = new HashMap<>();
101104

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

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,7 @@
4343
import org.threeten.bp.format.DateTimeFormatter;
4444
import org.threeten.bp.format.DateTimeFormatterBuilder;
4545
import org.threeten.bp.format.DateTimeParseException;
46+
import org.threeten.extra.PeriodDuration;
4647

4748
/**
4849
* A value for a QueryParameter along with its type.
@@ -63,6 +64,7 @@
6364
* <li>BigDecimal: StandardSQLTypeName.NUMERIC
6465
* <li>BigNumeric: StandardSQLTypeName.BIGNUMERIC
6566
* <li>JSON: StandardSQLTypeName.JSON
67+
* <li>INTERVAL: StandardSQLTypeName.INTERVAL
6668
* </ul>
6769
*
6870
* <p>No other types are supported through that entry point. The other types can be created by
@@ -308,12 +310,26 @@ public static QueryParameterValue time(String value) {
308310

309311
/**
310312
* Creates a {@code QueryParameterValue} object with a type of DATETIME. Must be in the format
311-
* "yyyy-MM-dd HH:mm:ss.SSSSSS", e.g. ""2014-08-19 12:41:35.220000".
313+
* "yyyy-MM-dd HH:mm:ss.SSSSSS", e.g. "2014-08-19 12:41:35.220000".
312314
*/
313315
public static QueryParameterValue dateTime(String value) {
314316
return of(value, StandardSQLTypeName.DATETIME);
315317
}
316318

319+
/**
320+
* Creates a {@code QueryParameterValue} object with a type of INTERVAL. Must be in the canonical
321+
* format "[sign]Y-M [sign]D [sign]H:M:S[.F]", e.g. "123-7 -19 0:24:12.000006" or ISO 8601
322+
* duration format, e.g. "P123Y7M-19DT0H24M12.000006S"
323+
*/
324+
public static QueryParameterValue interval(String value) {
325+
return of(value, StandardSQLTypeName.INTERVAL);
326+
}
327+
328+
/** Creates a {@code QueryParameterValue} object with a type of INTERVAL. */
329+
public static QueryParameterValue interval(PeriodDuration value) {
330+
return of(value, StandardSQLTypeName.INTERVAL);
331+
}
332+
317333
/**
318334
* Creates a {@code QueryParameterValue} object with a type of ARRAY, and an array element type
319335
* based on the given class.
@@ -408,6 +424,8 @@ private static <T> String valueToStringOrNull(T value, StandardSQLTypeName type)
408424
return value.toString();
409425
case JSON:
410426
if (value instanceof String || value instanceof JsonObject) return value.toString();
427+
case INTERVAL:
428+
if (value instanceof String || value instanceof PeriodDuration) return value.toString();
411429
break;
412430
case STRUCT:
413431
throw new IllegalArgumentException("Cannot convert STRUCT to String value");

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

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,8 @@ public enum StandardSQLTypeName {
5757
DATETIME,
5858
/** Represents a set of geographic points, represented as a Well Known Text (WKT) string. */
5959
GEOGRAPHY,
60-
/** Represents JSON data */
61-
JSON
60+
/** Represents JSON data. */
61+
JSON,
62+
/** Represents duration or amount of time. */
63+
INTERVAL
6264
}

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@
2727
import com.google.gson.JsonObject;
2828
import java.math.BigDecimal;
2929
import java.text.ParseException;
30+
import java.time.Period;
3031
import java.util.Date;
3132
import java.util.HashMap;
3233
import java.util.List;
@@ -38,6 +39,7 @@
3839
import org.threeten.bp.format.DateTimeFormatter;
3940
import org.threeten.bp.format.DateTimeFormatterBuilder;
4041
import org.threeten.bp.jdk8.Jdk8Methods;
42+
import org.threeten.extra.PeriodDuration;
4143

4244
public class QueryParameterValueTest {
4345

@@ -212,6 +214,24 @@ public void testJson() {
212214
assertThat(value1.getArrayType()).isNull();
213215
}
214216

217+
@Test
218+
public void testInterval() {
219+
QueryParameterValue value = QueryParameterValue.interval("123-7 -19 0:24:12.000006");
220+
QueryParameterValue value1 = QueryParameterValue.interval("P123Y7M-19DT0H24M12.000006S");
221+
QueryParameterValue value2 =
222+
QueryParameterValue.interval(
223+
PeriodDuration.of(Period.of(1, 2, 25), java.time.Duration.ofHours(8)));
224+
assertThat(value.getValue()).isEqualTo("123-7 -19 0:24:12.000006");
225+
assertThat(value1.getValue()).isEqualTo("P123Y7M-19DT0H24M12.000006S");
226+
assertThat(value2.getValue()).isEqualTo("P1Y2M25DT8H");
227+
assertThat(value.getType()).isEqualTo(StandardSQLTypeName.INTERVAL);
228+
assertThat(value1.getType()).isEqualTo(StandardSQLTypeName.INTERVAL);
229+
assertThat(value2.getType()).isEqualTo(StandardSQLTypeName.INTERVAL);
230+
assertThat(value.getArrayType()).isNull();
231+
assertThat(value1.getArrayType()).isNull();
232+
assertThat(value2.getArrayType()).isNull();
233+
}
234+
215235
@Test
216236
public void testBytes() {
217237
QueryParameterValue value = QueryParameterValue.bytes(new byte[] {1, 3});

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

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -130,6 +130,7 @@
130130
import java.nio.charset.StandardCharsets;
131131
import java.nio.file.FileSystems;
132132
import java.time.Instant;
133+
import java.time.Period;
133134
import java.util.ArrayList;
134135
import java.util.Collection;
135136
import java.util.Collections;
@@ -151,6 +152,7 @@
151152
import org.junit.Test;
152153
import org.junit.rules.Timeout;
153154
import org.threeten.bp.Duration;
155+
import org.threeten.extra.PeriodDuration;
154156

155157
public class ITBigQueryTest {
156158

@@ -816,6 +818,77 @@ public void testJsonType() throws InterruptedException {
816818
}
817819
}
818820

821+
@Test
822+
public void testIntervalType() throws InterruptedException {
823+
String tableName = "test_create_table_intervaltype";
824+
TableId tableId = TableId.of(DATASET, tableName);
825+
Schema schema = Schema.of(Field.of("intervalField", StandardSQLTypeName.INTERVAL));
826+
StandardTableDefinition standardTableDefinition = StandardTableDefinition.of(schema);
827+
try {
828+
// Create a table with a JSON column
829+
Table createdTable = bigquery.create(TableInfo.of(tableId, standardTableDefinition));
830+
assertNotNull(createdTable);
831+
832+
// Insert 3 rows of Interval data into the Interval column
833+
Map<String, Object> intervalRow1 =
834+
Collections.singletonMap("intervalField", "123-7 -19 0:24:12.000006");
835+
Map<String, Object> intervalRow2 =
836+
Collections.singletonMap("intervalField", "P123Y7M-19DT0H24M12.000006S");
837+
838+
InsertAllRequest request =
839+
InsertAllRequest.newBuilder(tableId).addRow(intervalRow1).addRow(intervalRow2).build();
840+
InsertAllResponse response = bigquery.insertAll(request);
841+
assertFalse(response.hasErrors());
842+
assertEquals(0, response.getInsertErrors().size());
843+
844+
// Insert another Interval row parsed from a String with Interval positional query parameter
845+
String dml = "INSERT INTO " + tableId.getTable() + " (intervalField) VALUES(?)";
846+
// Parsing from ISO 8610 format String
847+
QueryParameterValue intervalParameter =
848+
QueryParameterValue.interval("P125Y7M-19DT0H24M12.000006S");
849+
QueryJobConfiguration dmlQueryJobConfiguration =
850+
QueryJobConfiguration.newBuilder(dml)
851+
.setDefaultDataset(DatasetId.of(DATASET))
852+
.setUseLegacySql(false)
853+
.addPositionalParameter(intervalParameter)
854+
.build();
855+
bigquery.query(dmlQueryJobConfiguration);
856+
Page<FieldValueList> rows = bigquery.listTableData(tableId);
857+
assertEquals(3, Iterables.size(rows.getValues()));
858+
859+
// Parsing from threeten-extra PeriodDuration
860+
QueryParameterValue intervalParameter1 =
861+
QueryParameterValue.interval(
862+
PeriodDuration.of(Period.of(1, 2, 25), java.time.Duration.ofHours(8)));
863+
QueryJobConfiguration dmlQueryJobConfiguration1 =
864+
QueryJobConfiguration.newBuilder(dml)
865+
.setDefaultDataset(DatasetId.of(DATASET))
866+
.setUseLegacySql(false)
867+
.addPositionalParameter(intervalParameter1)
868+
.build();
869+
bigquery.query(dmlQueryJobConfiguration1);
870+
Page<FieldValueList> rows1 = bigquery.listTableData(tableId);
871+
assertEquals(4, Iterables.size(rows1.getValues()));
872+
873+
// Query the Interval column with Interval positional query parameter
874+
String sql = "SELECT intervalField FROM " + tableId.getTable() + " WHERE intervalField = ? ";
875+
QueryParameterValue intervalParameter2 =
876+
QueryParameterValue.interval("P125Y7M-19DT0H24M12.000006S");
877+
QueryJobConfiguration queryJobConfiguration =
878+
QueryJobConfiguration.newBuilder(sql)
879+
.setDefaultDataset(DatasetId.of(DATASET))
880+
.setUseLegacySql(false)
881+
.addPositionalParameter(intervalParameter2)
882+
.build();
883+
TableResult result = bigquery.query(queryJobConfiguration);
884+
for (FieldValueList values : result.iterateAll()) {
885+
assertEquals("125-7 -19 0:24:12.000006", values.get(0).getValue());
886+
}
887+
} finally {
888+
assertTrue(bigquery.delete(tableId));
889+
}
890+
}
891+
819892
@Test
820893
public void testCreateTableWithConstraints() {
821894
String tableName = "test_create_table_with_constraints";

‎pom.xml‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,12 +93,20 @@
9393
<version>${google-api-services-bigquery.version}</version>
9494
</dependency>
9595

96+
<!-- Used for JSON type -->
9697
<dependency>
9798
<groupId>com.google.code.gson</groupId>
9899
<artifactId>gson</artifactId>
99100
<version>2.8.9</version>
100101
</dependency>
101102

103+
<!-- Used for Interval and Range types -->
104+
<dependency>
105+
<groupId>org.threeten</groupId>
106+
<artifactId>threeten-extra</artifactId>
107+
<version>1.7.0</version>
108+
</dependency>
109+
102110
<!-- Test dependencies -->
103111
<dependency>
104112
<groupId>junit</groupId>

0 commit comments

Comments
 (0)