Scalar Functions and Timezones

I continue studying up for my OCA SQL Expert exam. Today I want to talk about scalar functions. These are also called single row functions. But before we dive into them, I want to revisit DUAL. This is a table specific to Oracle. It only has one column DUMMY of type VARCHAR2(1). That column has the value 'X'. You can use dual when you want to issue a select but do not want to go against any other table.

Now back to scalar functions. An example math scalar function is MOD. It stands for modulus. It is the remainder left over when you divide integers. A similar function is REMAINDER. However REMAINDER will find the closet divisor, possibly going past when evenly divides, and might return a negative number.

How about we talk about timezones? Here are three types:

1. UTC - Universal timezone, previously GMT
2. Database - the timezone on the server returned by DBTIMEZONE()
3. Session - the timezone of the user returned by SESSIONTIMEZONE()

Recall that a TIMESTAMP is a date plus fractional seconds. Well you can also have TIMESTAMP WITH TIMEZONE. This has a TIMESTAMP plus details on the timezone. The timezone info is stored in a UTC offset or a region name. There is also TIMESTAMP WIT?H LOCAL TIMEZONE. This is the time normalized to the database timezone.

The CURRENT_DATE() and CURRENT_TIMESTAMP() functions returns values in session timezone. The FROM_TZ() produces a TIMESTAMP WITH TIMEZONE. So does TO_TIMESTAMP_TZ(). You can use CAST() to change the type. You can EXTRACT() details of a timezone. And you can use AT TIME ZONE to transform a time to a different timezone.