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: 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.