FirebirdSQL logo

AT Time Zone Expression

Syntax
<at expr> ::= <expr> AT { TIME ZONE <time zone string> | LOCAL }

The AT expression expresses a datetime value in a different time zone, while keeping the same UTC instant.

AT translates a time/timestamp value to its corresponding value in another time zone.If LOCAL is used, the value is converted to the session time zone.

When expr is a WITHOUT TIME ZONE type, expr is first converted to a WITH TIME ZONE in the session time zone and then transformed to the specified time zone.

Examples
select time '12:00 GMT' at time zone '-03:00' from rdb$database;
select current_timestamp at time zone 'America/Sao_Paulo' from rdb$database;
select timestamp '2018-01-01 12:00 GMT' at local from rdb$database;

Conditional Expressions

A conditional expression is one that returns different values according to how a certain condition is met.It is composed by applying a conditional function construct, of which Firebird supports several.This section describes only one conditional expression construct: CASE.All other conditional expressions apply internal functions derived from CASE and are described in Conditional Functions.

CASE

The CASE construct returns a single value from a number of possible values.Two syntactic variants are supported:

  • The simple CASE, comparable to a case construct in Pascal or a switch in C

  • The searched CASE, which works like a series of “if …​ else if …​ else if” clauses.

Simple CASE
Syntax
...
CASE <test-expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END
...

When this variant is used, test-expr is compared to the first expr, second expr and so on, until a match is found, and the corresponding result is returned.If no match is found, defaultresult from the optional ELSE clause is returned.If there are no matches and no ELSE clause, NULL is returned.

The matching works as the “=” operator.That is, if test-expr is NULL, it does not match any expr, not even an expression that resolves to NULL.

The returned result does not have to be a literal value: it might be a field or variable name, compound expression or NULL literal.

Example
SELECT
  NAME,
  AGE,
  CASE UPPER(SEX)
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
    ELSE 'Unknown'
  END GENDER,
RELIGION
    FROM PEOPLE

A short form of the simple CASE construct is the DECODE function.