Valid values for timezone are either the name of a time zone (for example
‘Europe/Vienna’) or the UTC offset of a time zone (for example ‘+01:00’). To
get a complete overview of all possible values take a look at the available
time zones supported by Joda-Time.

The following example shows how to use the date_trunc function to generate a
day based histogram in the Europe/Moscow timezone:

The CURRENT_TIMESTAMP expression returns the timestamp in milliseconds
since epoch at the time the SQL statement was handled. Therefore, the same
timestamp value is returned for every invocation of a single statement.

Note

If the CURRENT_TIMESTAMP function is used in
Generated Columns it behaves slightly different in
UPDATE operations. In such a case the actual timestamp of each row
update is returned.

synopsis:

CURRENT_TIMESTAMP[(precision)]

precision must be a positive integer between 0 and 3. The default value is
3. It determines the number of fractional seconds to output. A value of 0 means
the timestamp will have second precision, no fractional seconds (milliseconds)
are given.

Note

The CURRENT_TIMESTAMP will be evaluated using javas
System.currentTimeMillis(). So its actual result depends on the
underlying operating system.

Valid values for timezone are either the name of a time zone (for example
‘Europe/Vienna’) or the UTC offset of a time zone (for example ‘+01:00’). To
get a complete overview of all possible values take a look at the available
time zones supported by Joda-Time.

The distance function can be used to calculate the distance between two
points on earth. It uses the Haversine formula which gives great-circle
distances between 2 points on a sphere based on their latitude and longitude.

The return value is the distance in meters.

Below is an example of the distance function where both points are specified
using WKT. See geo_point for more information on the implicit
type casting of geo points:

This scalar function can always be used in both the WHERE and ORDERBY
clauses. With the limitation that one of the arguments must be a literal and
the other argument must be a column reference.

Note

The algorithm of the calculation which is used when the distance function
is used as part of the result column list has a different precision than
what is stored inside the index which is utilized if the distance function
is part of a WHERE clause.

For example if select distance(...) returns 0.0 an equality check with
where distance(...) = 0 might not yield anything at all due to the
precision difference.

The latitude and longitude function return the coordinates of latitude
or longitude of a point, or NULL if not available. The input must be a
column of type geo_point, a valid WKT string or a double-array. See
geo_point for more information on the implicit type casting of
geo points.

Returns a GeoHash representation
based on full precision (12 characters) of the input point, or NULL if not
available. The input has to be a column of type geo_point, a valid WKT
string or a double-array.See geo_point for more information of
the implicit type casting of geo points.

regexp_replace can be used to replace every (or only the first) occurence
of a subsequence matching pattern in the source string with the
replacement string. If no subsequence in source matches the regular
expression pattern, source is returned unchanged.

The replacement string may contain expressions like $N where N is a
digit between 0 and 9. It references the Nth matched group of pattern
and the matching subsequence of that group will be inserted in the returned
string. The expression $0 will insert the whole matching source.

Per default, only the first occurrence of a subsequence matching pattern
will be replaced. If all occurrences shall be replaced use the g flag.

Appending to arrays with array_cat in updates is handy, but unfortunately
not isolated. We use optimistic concurrency control to ensure that your
update operation used the latest state of the row. But only 3 retry attempts
are made by fetching the newest version again and if they all fail, the
query fails.

The case expression is a generic conditional expression similar to if/else
statements in other programming languages and can be used wherever an
expression is valid.

CASEWHENconditionTHENresult[WHEN...][ELSEresult]END

Each condition expression must result in a boolean value. If the condition’s
result is true, the value of the result expression that follows the condition
will be the final result of the case expression and the subsequent when
branches will not be processed. If the condition’s result is not true, any
subsequent when clauses are examined in the same manner. If no when
condition yields true, the value of the case expression is the result of
the else clause. If the else clause is omitted and no condition is
true, the result is null.

The if function is a conditional function comparing to if statements of
most other programming languages. If the given condition expresion evaluates
to true, the result expression is evaluated and it’s value is returned. If
the condition evaluates to false, the result expression is not evaluated
and the optional given default expression is evaluated instead and it’s value
will be returned. If the default argument is omitted, NULL will be returned
instead.

The greatest function takes one or more arguments of the same type and will
return the largest value of these. NULL values in the arguments list are
ignored. The result will be NULL only if all the arguments evaluate to NULL.

The least function takes one or more arguments of the same type and will
return the smallest value of these. NULL values in the arguments list are
ignored. The result will be NULL only if all the arguments evaluate to NULL.