Using Timestamp functions in queries

You can perform various operations on the timestamp and duration values.

You can add a duration to a timestamp, find the difference between two timestamps, and round timestamp to a specified unit. You can cast a timestamp to/from string with customized patterns. Some of the functions support the extraction of the date part of a timestamp. You can also use these functions to display the current time.

The following timestamp functions are supported:

Table 1 - Timestamp Functions

Function Description
timestamp_add Adds a duration to a timestamp value.
timestamp_diff Returns the number of milliseconds between two timestamp values.
get_duration Converts the given number of milliseconds to a duration string.
timestamp_ceil Rounds-up the timestamp value to the specified unit.
timestamp_floor/timestamp_trunc Rounds-down the timestamp value to the specified unit.
timestamp_round Rounds the timestamp value to the specified unit.
timestamp_bucket Rounds the timestamp value to the beginning of the specified interval, starting from a specified origin value.
format_timestamp Converts a timestamp into a string according to the specified pattern and the timezone.
parse_to_timestamp Converts a string in the specified pattern into a timestamp value.
to_last_day_of_month Returns the last day of the month from a given timestamp.
Timestamp extract functions

Extracts the corresponding date part of a given timestamp. The following functions are supported:

  • year
  • month
  • day
  • hour
  • minute
  • second
  • millisecond
  • microsecond
  • nanosecond

Returns the week number within the year. The following functions are supported:

  • week
  • isoweek

Returns the corresponding index from a given timestamp. The following functions are supported:

  • quarter
  • day_of_week
  • day_of_month
  • day_of_year
current_time_millis Returns the current time as the number of milliseconds.
current_time Returns the current time as a timestamp value.

If you want to follow along with the examples, see Sample data to run queries to view a sample data and use the scripts to load sample data for testing. The scripts create the tables used in the examples and load data into the tables.

If you want to follow along with the examples, see Sample data to run queries to view a sample data and learn how to use OCI console to create the example tables and load data using JSON files.

Timestamp Arithmetic Functions

You can use timestamp_add, timestamp_diff, or get_duration functions to perform arithmetic operations on the timestamp and duration values.

Example 1 - In the airline application, a buffer of five minutes delay is considered “on time”. Print the estimated arrival time on the first leg with a buffer of five minutes for the passenger with ticket number 1762399766476.

SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
AS ARRIVAL_TIME FROM BaggageInfo bag
WHERE ticketNo=1762399766476

Explanation :In the airline application, a customer can have any number of flight legs depending on the source and destination. In the query above, you are fetching the estimated arrival in the “first leg” of the travel. So the first record of the flightsLeg array is fetched and the estimatedArrival time is fetched from the array and a buffer of “5 minutes” is added to that and displayed.

Output:

{"ARRIVAL_TIME":"2019-02-03T06:05:00.000000000Z"}

Note:

The column estimatedArrival is a STRING. If the column has STRING values in ISO-8601 format, then it will be automatically converted by the SQL runtime into TIMESTAMP data type.

ISO8601 describes an internationally accepted way to represent dates, times, and durations.

Syntax: Date with time: YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]][Z|(+|-)hh:mm]

Where:

Example 2 - Print the estimated arrival time in every leg with a buffer of five minutes for the passenger with ticket number 1762399766476.

SELECT $s.ticketno, $value as estimate,
timestamp_add($value, '5 minute') AS add5min
FROM baggageinfo $s,
$s.bagInfo.flightLegs.estimatedArrival as $value
WHERE ticketNo=1762399766476

Explanation:You want to display the estimatedArrival time on every leg. The number of legs can be different for every customer. So variable reference is used in the query above and the baggageInfo array and the flightLegs array are unnested to execute the query.

Output:

{"ticketno":1762399766476,"estimate":"2019-02-03T06:00:00Z",
"add5min":"2019-02-03T06:05:00.000000000Z"}
{"ticketno":1762399766476,"estimate":"2019-02-03T08:22:00Z",
"add5min":"2019-02-03T08:27:00.000000000Z"}

Example 3 - How many bags arrived in the last week?

SELECT count(*) AS COUNT_LASTWEEK FROM baggageInfo bag
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate < current_time()
AND $element.bagArrivalDate > timestamp_add(current_time(), "-7 days")]

Explanation:You get a count of the number of bags processed by the airline application in the last week. A customer can have more than one bag( that is bagInfo array can have more than one record). ThebagArrivalDate should have a value between today and the last 7 days. For every record in the bagInfo array, you determine if the bag arrival time is between the time now and one week ago. The function current_time gives you the time now. An EXISTS condition is used as a filter for determining if the bag has an arrival date in the last week. The count function determines the total number of bags in this time period.

Output:

{"COUNT_LASTWEEK":0}

Example 4 - Find the number of bags arriving in the next 6 hours.

SELECT count(*) AS COUNT_NEXT6HOURS FROM baggageInfo bag
WHERE EXISTS bag.bagInfo[$element.bagArrivalDate > current_time()
AND $element.bagArrivalDate < timestamp_add(current_time(), "6 hours")]

Explanation:You get a count of the number of bags that will be processed by the airline application in the next 6 hours. A customer can have more than one bag( that isbagInfo array can have more than one record). The bagArrivalDate should be between the time now and the next 6 hours. For every record in the bagInfo array, you determine if the bag arrival time is between the time now and six hours later. The function current_time gives you the time now. An EXISTS condition is used as a filter for determining if the bag has an arrival date in the next six hours. The count function determines the total number of bags in this time period.

Output:

{"COUNT_NEXT6HOURS":0}

Example 5 - What is the duration between the time the baggage was boarded at one leg and reached the next leg for the passenger with ticket number 1762355527825?

SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762355527825

Explanation:In an airline application every customer can have a different number of hops/legs between their source and destination. In this query, you determine the time taken between every flight leg. This is determined by the difference between bagArrivalDate and flightDate for every flight leg. To determine the duration in days or hours or minutes, pass the result of the timestamp_diff function to the get_duration function.

Output:

{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:00:00Z",
"diff":"3 hours 17 minutes"}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:23:00Z",
"diff":"2 hours 54 minutes"}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T08:23:00Z",
"diff":"1 hour 54 minutes"}

To determine the duration in milliseconds, use the timestamp_diff function.

SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate) AS diff
FROM baggageinfo $s,
$s.bagInfo[] AS $bagInfo,
$bagInfo.flightLegs[] AS $flightLeg
WHERE ticketNo=1762355527825

Output:

{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:00:00Z","diff":11820000}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T07:23:00Z","diff":10440000}
{"bagArrivalDate":"2019-03-22T10:17:00Z","flightDate":"2019-03-22T08:23:00Z","diff":6840000}

Example 6 - How long does it take from the time of check-in to the time the bag is scanned at the point of boarding for the passenger with ticket number 176234463813?

SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
get_duration(timestamp_diff(
    $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
    $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
)) AS diff
FROM baggageinfo $s,
$s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 AND
starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)

Explanation:In the baggage data, every flightLeg has an actions array. There are three different actions in the action array. The action code for the first element in the array is Checkin/Offload. For the first leg, the action code is Checkin and for the other legs, the action code is Offload at the hop. The action code for the second element of the array is BagTag Scan. In the query above, you determine the difference in action time between the bag tag scan and check-in time. You use the contains function to filter the action time only if the action code is Checkin or BagScan. Since only the first flight leg has details of check-in and bag scan, you additionally filter the data using starts_with function to fetch only the source code fltRouteSrc. To determine the duration in days or hours or minutes, pass the result of the timestamp_diff function to the get_duration function.

To determine the duration in milliseconds, use the timestamp_difffunction.

SELECT $flightLeg.flightNo,
$flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime AS checkinTime,
$flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime AS bagScanTime,
timestamp_diff(
   $flightLeg.actions[contains($element.actionCode, "Checkin")].actionTime,
   $flightLeg.actions[contains($element.actionCode, "BagTag Scan")].actionTime
) AS diff
FROM baggageinfo $s,
$s.bagInfo[].flightLegs[] AS $flightLeg
WHERE ticketNo=176234463813 AND
starts_with($s.bagInfo[].routing, $flightLeg.fltRouteSrc)

Output:

{"flightNo":"BM572","checkinTime":"2019-03-02T03:28:00Z",
"bagScanTime":"2019-03-02T04:52:00Z","diff":"- 1 hour 24 minutes"}

Example 7 - How long does it take for the bags of a customer with ticket no 1762320369957 to reach the first transit point?

SELECT  $bagInfo.flightLegs[1].actions[2].actionTime,
$bagInfo.flightLegs[0].actions[0].actionTime,
get_duration(timestamp_diff($bagInfo.flightLegs[1].actions[2].actionTime,
                            $bagInfo.flightLegs[0].actions[0].actionTime)) AS diff
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo
WHERE ticketNo=1762320369957

Explanation:In an airline application every customer can have a different number of hops/legs between their source and destination. In the example above, you determine the time taken for the bag to reach the first transit point. In the baggage data, the flightLeg is an array. The first record in the array refers to the first transit point details. The flightDate in the first record is the time when the bag leaves the source and the estimatedArrival in the first flight leg record indicates the time it reaches the first transit point. The difference between the two gives the time taken for the bag to reach the first transit point. To determine the duration in days or hours or minutes, pass the result of the timestamp_diff function to the get_duration function.

To determine the duration in milliseconds, use the timestamp_diff function.

SELECT  $bagInfo.flightLegs[0].flightDate,
$bagInfo.flightLegs[0].estimatedArrival,
timestamp_diff($bagInfo.flightLegs[0].estimatedArrival,
$bagInfo.flightLegs[0].flightDate) AS diff
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo
WHERE ticketNo=1762320369957

Output:

{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:00:00Z","diff":"13 hours"}
{"flightDate":"2019-03-12T03:00:00Z","estimatedArrival":"2019-03-12T16:40:00Z","diff":"13 hours 40 minutes"}

Timestamp Round Functions

You can use timestamp_ceil, timestamp_floor, timestamp_trunc, timestamp_round, and timestamp_bucket functions to round the timestamp values.

For timestamp_ceil, timestamp_floor, timestamp_trunc, and timestamp_round functions, you must supply a unit as the second argument. The unit specifies the precision to be considered while rounding the input timestamp.

The following units are supported in either singular or plural format: YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND.

You can use the timestamp_bucket function to round the given timestamp value to the beginning of the specified interval (bucket). The interval starts at a specified origin on the timeline.

The timestamp_bucket supports the following intervals in either singular or plural format: WEEK, DAY, HOUR, MINUTE, SECOND.

Example 1 - From airline baggage tracking data, print the bag arrival date and the bag auction date for a passenger with ticket number 1762344493810, considering 90 days as the luggage retention period.

SELECT $b.bagArrivalDate AS BagArrival,
timestamp_ceil(timestamp_add($b.bagArrivalDate, "90 Days"), 'day') AS BagCollection
FROM BaggageInfo bag, bag.bagInfo AS $b
WHERE ticketNo=1762344493810

Explanation: This query shows how to nest the timestamp functions. To determine the date an unclaimed bag is retained, add 90 days to the bagArrivalDate using the timestamp_add function. The timestamp_ceil function rounds up the value to the beginning of the next day.

Output:

{"BagArrival":"2019-02-01T16:13:00Z","BagCollection":"2019-05-03T00:00:00Z"}

Example 2 - Print the name, flight number, and travel date for all the passengers who boarded at originating airport JFK in the month of March 2019.

SELECT bag.fullName, $f.flightNo, $f.flightDate
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
WHERE $f.fltRouteSrc = "JFK" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-03-01'

Explanation: You use the timestamp_floor function with the unit value as MONTH to round down the travel dates to the beginning of the month. You then compare the resulting timestamp value with the string “2019-03-01” to select the desired passengers. This query does not consider the passengers in transit.

This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

To avoid the duplication of results due to multiple checked bags by a passenger, you consider only the first element of the bagInfo array in this query.

Output:

{"fullName":"Kendal Biddle","flightNo":"BM127","flightDate":"2019-03-04T06:00:00Z"}
{"fullName":"Dierdre Amador","flightNo":"BM495","flightDate":"2019-03-07T07:00:00Z"}

Example 3 - From the airline baggage tracking data, print all the activities performed on the checked bags in the originating station MEL. Align the actions to one minute interval.

SELECT $b.actionAt,
       $b.actionCode,
       timestamp_round($b.actionTime, 'MINUTE') as actionTime
FROM baggageInfo bag, bag.bagInfo[0].flightLegs[0].actions[] AS $b
WHERE bag.bagInfo[0].flightLegs[0].fltRouteSrc = "MEL"

Explanation: In this query, you use the timestamp_round function with unit as MINUTE to round the actionTime to the nearest minute.

To avoid the duplication of results due to multiple checked baggage by a passenger, you consider only the first element of the bagInfo array in this query.

Output:

{"actionAt":"MEL","actionCode":"ONLOAD to LAX","actionTime":"2019-03-01T12:20:00Z"}
{"actionAt":"MEL","actionCode":"BagTag Scan at MEL","actionTime":"2019-03-01T11:52:00Z"}
{"actionAt":"MEL","actionCode":"Checkin at MEL","actionTime":"2019-03-01T11:43:00Z"}

Example 4 - Fetch the statistics of the number of passengers departing from the IST airport every 12 hrs with buckets starting from January 1st, 2019. Consider data only for the month of February 2019.

SELECT $t AS DATE,
count($t) AS FLIGHTCOUNT
FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[] $f,
timestamp_bucket($f.flightDate, '12 HOURS', '2019-01-01T00') $t
WHERE $f.fltRouteSrc =any "IST" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-02-01T00:00:00Z'
GROUP BY $t
ORDER BY $t

Explanation: To consider passengers traveling in February 2019, use the timestamp_floor function and round down the flightDate to the beginning of the month. Compare the result with the string “2019-02-01T00:00:00Z”. This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

To include the transit flights from the IST airport, use the array constructor [ ] to indicate that the flightLegs is an array and consider each fltRouteSrc array element in the search.

Use the timsestamp_bucket function on the flightDate fields with interval as 12 hours and origin as 1st of January 2019.

Output:

{"DATE":"2019-02-02T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T00:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-04T12:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-07T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-11T12:00:00.000000000Z","FLIGHTCOUNT":1}
{"DATE":"2019-02-12T00:00:00.000000000Z","FLIGHTCOUNT":2}
{"DATE":"2019-02-12T12:00:00.000000000Z","FLIGHTCOUNT":1}

Timestamp Format Functions

You can use format_timestamp and parse_to_timestamp functions to format timestamp values. Also, you can use the to_last_day_of_month function to fetch the last day of the month from a given timestamp.

Example 1 - For a passenger with a specific ticket number, print the estimated arrival time on the first leg according to the pattern and the timezone entered.

SELECT $info.estimatedArrival,
format_timestamp($info.estimatedArrival, "MMM dd, yyyy HH:mm:ss O", "America/Vancouver") AS FormattedTimestamp
FROM BaggageInfo bag, bag.bagInfo.flightLegs[0] AS $info
WHERE ticketNo= 1762399766476

Explanation: In this query, you specify the estimatedArrival field, pattern, and full name of the timezone as arguments to the format_timestamp function to convert the timestamp string to the specified “MMM dd, yyyy HH:mm:ss” pattern.

Note: The letter ‘O’ in the pattern argument represents the ZoneOffset, which prints the amount of time that differs from Greenwich/UTC in the resulting string.

Output:

{"estimatedArrival":"2019-02-03T06:00:00Z","FormattedTimestamp":"Feb 02, 2019 22:00:00 GMT-8"}

Example 2 - Parse the given string with the specified pattern, which includes a zone offset, into a timestamp.

SELECT format_timestamp(parse_to_timestamp('2024/02/12 18:30:54 GMT+02:00', "yyyy/dd/MM HH:mm:ss OOOO"),"yyyy-MM-dd HH:mm:ss OOOO","GMT+02:00")AS TIMESTAMP
FROM BaggageInfo
WHERE ticketNo=1762390789239

Explanation: In this query, the string argument has a TimeZoneID, GMT+02:00, so the pattern argument must include a zone symbol or a ZoneOffset. When wrapped in the format_timestamp function, the output timestamp will display in the GMT+02:00 timezone.

Output:

{"TIMESTAMP":"2024-12-02 18:30:54 GMT+02:00"}

Example 3 - For a subscriber, print the last day of the month in which the account subscription expires.

SELECT sa.acct_id, to_last_day_of_month(sa.account_expiry) AS lastday FROM stream_acct sa WHERE profile_name="DM"

Output:

{"acct_id":4,"lastday":"2024-03-31T00:00:00Z"}

Timestamp Extract Functions

Timestamp extract functions fetch the corresponding date, week, or the index value from a given timestamp.

Date extract functions return the corresponding year/month/day/hour/minute/second/millisecond/microsecond/nanosecond from a timestamp.

Example 1 - Get consolidated travel details of the passengers from airline baggage tracking data.

In an airline application, it is beneficial to the passengers to have a quick summary of their upcoming travel details. You can use miscellaneous time functions to get consolidated travel details of the passengers from the BaggageInfo table.

SELECT DISTINCT
$s.fullName,
$s.bagInfo[].flightLegs[].flightNo AS flightnumbers,
$s.bagInfo[].flightLegs[].fltRouteSrc AS From,
concat ($t1,":", $t2,":", $t3) AS Traveldate
FROM baggageinfo $s, $s.bagInfo[].flightLegs[].flightDate AS $bagInfo,
day(CAST($bagInfo AS Timestamp(0))) $t1,
month(CAST($bagInfo AS Timestamp(0))) $t2,
year(CAST($bagInfo AS Timestamp(0))) $t3

Explanation:

You can use the time functions to retrieve the travel date, month, and year. The concat string function is used to concatenate the retrieved travel records to display them in the desired format on the application. You first use the CAST expression to convert the flightDates to a TIMESTAMP and then fetch the date, month, and year details from the timestamp.

Output:

{"fullName":"Adam Phillips","flightnumbers":["BM604","BM667"],"From":["MIA","LAX"],"Traveldate":"1:2:2019"}

{"fullName":"Adelaide Willard","flightnumbers":["BM79","BM907"],"From":["GRU","ORD"],"Traveldate":"15:2:2019"}

The query returns the flight details which can serve as a quick look-up for the passengers.

Week extract functions return the corresponding week/isoweek from a timestamp.

Example 2 - Determine the week and ISO week number from a passenger’s travel date.

SELECT
$s.fullName,
$s.contactPhone,
week(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS TravelWeek,
isoweek(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS ISO_TravelWeek
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explanation: You first use the CAST expression to convert the flightDate to a TIMESTAMP and then fetch the week and isoweek from the timestamp.

Output:

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","TravelWeek":7,"ISO_TravelWeek":7}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","TravelWeek":5,"ISO_TravelWeek":5}

Timestamp index extract functions return the corresponding quarter/week/month/year index from a timestamp.

Example 3 - Find the day of the week for given timestamps.

SELECT day_of_week("2024-06-19") AS DAYVAL1,
day_of_week(parse_to_timestamp('06/19/24', 'MM/dd/yy')) AS DAYVAL2
FROM BaggageInfo
WHERE ticketNo=1762344493810

Explanation: The second timestamp in the query is in an unsupported format ‘06/19/24’ by itself, so wrap it in the parse_to_timestamp function to make it valid.

Output:

{
  "DAYVAL1" : 3,
  "DAYVAL2" : 3
}

Current Time Functions

You can use current_time_millis and current_time functions to fetch the current time. The current_time_millis function returns the time as the number of milliseconds. The current_time function returns the time as a timestamp value.

Example 1 - Determine the time lapse between the last travel date of a passenger and the current date.

In an airline application, a few customers travel very frequently and are entitled to frequent flier miles rewards. You can determine the time lapse between the last travel date of a passenger and the current date to assess if they can be considered for such a reward program.

SELECT
$s.fullName,
$s.contactPhone,
get_duration(timestamp_diff(current_time(), CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0)))) AS LastTravel
FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explanation:

You can use the current_time function to get the current time. To determine the timespan between the last travel date and the current date, you can supply the current time to the get_duration/timestamp_diff function along with the last travel time. For more details on timestamp_diff and get_duration functions.

Output:

{"fullName":"Adelaide Willard","contactPhone":"421-272-8082","LastTravel":"1453 days 6 hours 20 minutes 56 seconds 601 milliseconds"}

{"fullName":"Adam Phillips","contactPhone":"893-324-1064","LastTravel":"1451 days 23 hours 19 minutes 39 seconds 543 milliseconds"}

You use the current_time function to calculate the current time. Use the timestamp_diff function to calculate the time difference between the current time and the last flight date. You first use the CAST expression to convert the flightDates to a TIMESTAMP and then fetch the day, month, and year details from the timestamp. Since the timestamp_diff function returns the number of milliseconds between two timestamp values, you then use the get_duration function to convert the milliseconds to a duration string.

The get_duration function converts the milliseconds to days, hours, minutes, seconds, and milliseconds based on the return value. The following conversions are considered for calculation purposes:

1000 milliseconds = 1 second
60 seconds = 1 minute
60 minutes = 1 hour
24 hours = 1 day

For example: If the timestamp_diff function returns the value 129084684821 milliseconds, the get_duration function converts it correspondingly to 1494 days 52 minutes 4 seconds 687 milliseconds.

Examples using QueryRequest API

You can use QueryRequest API and apply SQL functions to fetch data from a NoSQL table.

To execute your query, you use the NoSQLHandle.query() API.

Download the full code SQLFunctions.java from the examples here.

 //Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)){
      for (MapValue res : results) {
         System.out.println("\t" + res);
      }
   }
}
String ts_func1="SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")"+
                         " AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625";
System.out.println("Using timestamp_add function ");
fetchRows(handle,ts_func1);
String ts_func2="SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate, "+
                "get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff "+
                 "FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg "+
                 "WHERE ticketNo=1762344493810";
System.out.println("Using get_duration and timestamp_diff function ");
fetchRows(handle,ts_func2);

To execute your query use the borneo.NoSQLHandle.query() method.

Download the full code SQLFunctions.py from the examples here.

# Fetch data from the table
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))
 ts_func1 = '''SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                 AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625'''
print('Using timestamp_add function:')
fetch_data(handle,ts_func1)

ts_func2 = '''SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
              get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
              FROM baggageinfo $s,
              $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
              WHERE ticketNo=1762344493810'''
print('Using get_duration and timestamp_diff function:')
fetch_data(handle,ts_func2)

To execute a query use the Client.Query function.

Download the full code SQLFunctions.go from the examples here.

 //fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
		 PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
         fmt.Printf("Query failed: %v\n", err)
  	return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
  	return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
      fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
}
ts_func1 := `SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625`
fmt.Printf("Using timestamp_add function::\n")
fetchData(client, err,tableName,ts_func1)

ts_func2 := `SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
             get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
             FROM baggageinfo $s,
             $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
             WHERE ticketNo=1762344493810`
fmt.Printf("Using get_duration and timestamp_diff function:\n")
fetchData(client, err,tableName,ts_func2)

To execute a query use query method.

JavaScript: Download the full code SQLFunctions.js from the examples here.

  //fetches data from the table
async function fetchData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

TypeScript: Download the full code SQLFunctions.ts from the examples here.

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}
const ts_func1 = `SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, "5 minutes")
                  AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625`
console.log("Using timestamp_add function:");
await fetchData(handle,ts_func1);

const ts_func2 = `SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
                  get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
                  FROM baggageinfo $s,
                  $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
                  WHERE ticketNo=1762344493810`
console.log("Using get_duration and timestamp_diff function:");
await fetchData(handle,ts_func2);

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

Download the full code SQLFunctions.cs from the examples here.

private static async Task fetchData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Rows)
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
}
private const string ts_func1 =@"SELECT timestamp_add(bag.bagInfo.flightLegs[0].estimatedArrival, ""5 minutes"")
                                       AS ARRIVAL_TIME FROM BaggageInfo bag WHERE ticketNo=1762341772625";
Console.WriteLine("\nUsing timestamp_add function!");
await fetchData(client,ts_func1);

private const string ts_func2 =@"SELECT $s.ticketno, $bagInfo.bagArrivalDate, $flightLeg.flightDate,
                                       get_duration(timestamp_diff($bagInfo.bagArrivalDate, $flightLeg.flightDate)) AS diff
                                       FROM baggageinfo $s,
                                       $s.bagInfo[] AS $bagInfo, $bagInfo.flightLegs[] AS $flightLeg
                                       WHERE ticketNo=1762344493810";
Console.WriteLine("\nUsing get_duration and timestamp_diff function!");
await fetchData(client,ts_func2);

Related Topics