1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Timestamps
  4. current_time function

current_time function

Returns the current time in UTC, as a timestamp value with millisecond precision.

Syntax:
timestamp(3) current_time()

Semantics:

  • This function does not expect any input argument.
  • Return Value: timestamp(3)

Example 2: Miscellaneous timestamp functions

Example 12-31 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, see timestamp_diff function.

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