Db2 Time and Date Functions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"top\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "# Db2 11 Time and Date Functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "There are plenty of new date and time functions found in Db2 11. These functions allow you to extract portions from a date\n",
    "and format the date in a variety of different ways. While Db2 already has a number of date and time functions, these new\n",
    "functions allow for greater compatibility with other database implementations, making it easier to port to DB2.ion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hideCode": false,
    "hideOutput": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%run db2.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "\n",
    "* [Extract Function](#extract)\n",
    "* [DATE_PART Function](#part)\n",
    "* [DATE_TRUNC Function](#trunc)\n",
    "* [Extracting Specific Days from a Month](#month)\n",
    "* [Date Addition](#add)\n",
    "* [Extracting Weeks, Months, Quarters, and Years](#extract)\n",
    "* [Next Day Function](#nextday)\n",
    "* [Between Date/Time Functions](#between)\n",
    "* [Months Between](#mbetween)\n",
    "* [Date Duration](#duration)\n",
    "* [Overlaps Predicate](#overlaps)\n",
    "* [UTC Time Conversions](#utc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='extract'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Extract Function\n",
    "\n",
    "The EXTRACT function extracts and element from a date/time value. The syntax of the EXTRACT command is:\n",
    "```Python\n",
    "EXTRACT( element FROM expression )\n",
    "```\n",
    "This is a slightly different format from most functions that you see in the DB2. Element must be one of the following values:\n",
    "\n",
    "|Element Name      | Description\n",
    "|:---------------- | :-----------------------------------------------------------------------------------------\n",
    "|EPOCH             | Number of seconds since 1970-01-01 00:00:00.00. The value can be positive or negative.\n",
    "|MILLENNIUM(S)     | The millennium is to be returned.\n",
    "|CENTURY(CENTURIES)| The number of full 100-year periods represented by the year.\n",
    "|DECADE(S)         | The number of full 10-year periods represented by the year.\n",
    "|YEAR(S)           | The year portion is to be returned. \n",
    "|QUARTER           | The quarter of the year (1 - 4) is to be returned.\n",
    "|MONTH             | The month portion is to be returned. \n",
    "|WEEK              | The number of the week of the year (1 - 53) that the specified day is to be returned.\n",
    "|DAY(S)            | The day portion is to be returned. \n",
    "|DOW               | The day of the week that is to be returned. Note that \"1\" represents Sunday. \n",
    "|DOY               | The day (1 - 366) of the year that is to be returned.\n",
    "|HOUR(S)           | The hour portion is to be returned. \n",
    "|MINUTE(S)         | The minute portion is to be returned. \n",
    "|SECOND(S)         | The second portion is to be returned. \n",
    "|MILLISECOND(S)    | The second of the minute, including fractional parts to one thousandth of a second\n",
    "|MICROSECOND(S)    | The second of the minute, including fractional parts to one millionth of a second\n",
    "\n",
    "The synonym NOW is going to be used in the next example. NOW is a synonym for CURRENT TIMESTAMP."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%sql VALUES NOW"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "This SQL will return every possible extract value from the current date.the SQL standard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%%sql -a\n",
    "WITH DATES(FUNCTION, RESULT) AS (\n",
    "  VALUES\n",
    "    ('EPOCH',              EXTRACT( EPOCH          FROM NOW )),          \n",
    "    ('MILLENNIUM(S)',      EXTRACT( MILLENNIUM     FROM NOW )),\n",
    "    ('CENTURY(CENTURIES)', EXTRACT( CENTURY        FROM NOW )),    \n",
    "    ('DECADE(S)',          EXTRACT( DECADE         FROM NOW )),\n",
    "    ('YEAR(S)',            EXTRACT( YEAR           FROM NOW )),    \n",
    "    ('QUARTER',            EXTRACT( QUARTER        FROM NOW )),    \n",
    "    ('MONTH',              EXTRACT( MONTH          FROM NOW )),\n",
    "    ('WEEK',               EXTRACT( WEEK           FROM NOW )),\n",
    "    ('DAY(S)',             EXTRACT( DAY            FROM NOW )),    \n",
    "    ('DOW',                EXTRACT( DOW            FROM NOW )),    \n",
    "    ('DOY',                EXTRACT( DOY            FROM NOW )),    \n",
    "    ('HOUR(S)',            EXTRACT( HOURS          FROM NOW )),    \n",
    "    ('MINUTE(S)',          EXTRACT( MINUTES        FROM NOW )),\n",
    "    ('SECOND(S)',          EXTRACT( SECONDS        FROM NOW )),\n",
    "    ('MILLISECOND(S)',     EXTRACT( MILLISECONDS   FROM NOW )),    \n",
    "    ('MICROSECOND(S)',     EXTRACT( MICROSECONDS   FROM NOW ))\n",
    "  )\n",
    "SELECT FUNCTION, CAST(RESULT AS BIGINT) FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='part'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## DATE_PART Function\n",
    "\n",
    "DATE_PART is similar to the EXTRACT function but it uses the more familiar syntax:\n",
    "\n",
    "```Python\n",
    "DATE_PART(element, expression)\n",
    "```\n",
    "\n",
    "In the case of the function, the element must be placed in quotes, rather than as a keyword in the EXTRACT function. in addition, the DATE_PART always returns a BIGINT, while the EXTRACT function will return a different data type depending on the element being returned. For instance, compare the SECONDs option for both functions. In the case of EXTRACT you get a DECIMAL result while for the DATE_PART you get a truncated BIGINT."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -a\n",
    "WITH DATES(FUNCTION, RESULT) AS (\n",
    "  VALUES\n",
    "    ('EPOCH',              DATE_PART('EPOCH'          ,NOW )),          \n",
    "    ('MILLENNIUM(S)',      DATE_PART('MILLENNIUM'     ,NOW )),\n",
    "    ('CENTURY(CENTURIES)', DATE_PART('CENTURY'        ,NOW )),    \n",
    "    ('DECADE(S)',          DATE_PART('DECADE'         ,NOW )),\n",
    "    ('YEAR(S)',            DATE_PART('YEAR'           ,NOW )),    \n",
    "    ('QUARTER',            DATE_PART('QUARTER'        ,NOW )),    \n",
    "    ('MONTH',              DATE_PART('MONTH'          ,NOW )),\n",
    "    ('WEEK',               DATE_PART('WEEK'           ,NOW )),\n",
    "    ('DAY(S)',             DATE_PART('DAY'            ,NOW )),    \n",
    "    ('DOW',                DATE_PART('DOW'            ,NOW )),    \n",
    "    ('DOY',                DATE_PART('DOY'            ,NOW )),    \n",
    "    ('HOUR(S)',            DATE_PART('HOURS'          ,NOW )),    \n",
    "    ('MINUTE(S)',          DATE_PART('MINUTES'        ,NOW )),\n",
    "    ('SECOND(S)',          DATE_PART('SECONDS'        ,NOW )),\n",
    "    ('MILLISECOND(S)',     DATE_PART('MILLISECONDS'   ,NOW )),    \n",
    "    ('MICROSECOND(S)',     DATE_PART('MICROSECONDS'   ,NOW ))\n",
    "  )\n",
    "SELECT FUNCTION, CAST(RESULT AS BIGINT) FROM DATES;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='trunc'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DATE_TRUNC Function\n",
    "\n",
    "DATE_TRUNC computes the same results as the DATE_PART function but then truncates the value down. Note that not all values can be truncated. The function syntax is:\n",
    "\n",
    "```Python\n",
    "DATE_TRUNC(element, expression)\n",
    "```\n",
    "\n",
    "The element must be placed in quotes, rather than as a keyword in the EXTRACT function.\n",
    "Note that DATE_TRUNC always returns a BIGINT.\n",
    "\n",
    "The elements that can be truncated are:\n",
    "\n",
    "|Element Name       |Description\n",
    "|:----------------  |:------------------------------------------------------------------------------\n",
    "|MILLENNIUM(S)      |The millennium is to be returned.\n",
    "|CENTURY(CENTURIES) |The number of full 100-year periods represented by the year.\n",
    "|DECADE(S)          |The number of full 10-year periods represented by the year.\n",
    "|YEAR(S)            |The year portion is to be returned. \n",
    "|QUARTER            |The quarter of the year (1 - 4) is to be returned.\n",
    "|MONTH              |The month portion is to be returned. \n",
    "|WEEK               |The number of the week of the year (1 - 53) that the specified day is to be returned.\n",
    "|DAY(S)             |The day portion is to be returned.  \n",
    "|HOUR(S)            |The hour portion is to be returned. \n",
    "|MINUTE(S)          |The minute portion is to be returned. \n",
    "|SECOND(S)          |The second portion is to be returned. \n",
    "|MILLISECOND(S)     |The second of the minute, including fractional parts to one thousandth of a second\n",
    "|MICROSECOND(S)     |The second of the minute, including fractional parts to one millionth of a secondry data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -a\n",
    "WITH DATES(FUNCTION, RESULT) AS (\n",
    "  VALUES         \n",
    "    ('MILLENNIUM(S)',      DATE_TRUNC('MILLENNIUM'     ,NOW )),\n",
    "    ('CENTURY(CENTURIES)', DATE_TRUNC('CENTURY'        ,NOW )),    \n",
    "    ('DECADE(S)',          DATE_TRUNC('DECADE'         ,NOW )),\n",
    "    ('YEAR(S)',            DATE_TRUNC('YEAR'           ,NOW )),    \n",
    "    ('QUARTER',            DATE_TRUNC('QUARTER'        ,NOW )),    \n",
    "    ('MONTH',              DATE_TRUNC('MONTH'          ,NOW )),\n",
    "    ('WEEK',               DATE_TRUNC('WEEK'           ,NOW )),\n",
    "    ('DAY(S)',             DATE_TRUNC('DAY'            ,NOW )),    \n",
    "    ('HOUR(S)',            DATE_TRUNC('HOURS'          ,NOW )),    \n",
    "    ('MINUTE(S)',          DATE_TRUNC('MINUTES'        ,NOW )),\n",
    "    ('SECOND(S)',          DATE_TRUNC('SECONDS'        ,NOW )),\n",
    "    ('MILLISECOND(S)',     DATE_TRUNC('MILLISECONDS'   ,NOW )),    \n",
    "    ('MICROSECOND(S)',     DATE_TRUNC('MICROSECONDS'   ,NOW ))\n",
    "  )\n",
    "SELECT FUNCTION, RESULT FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='month'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extracting Specfic Days from a Month\n",
    "\n",
    "There are three functions that retrieve day information from a date. These functions include:\n",
    "\n",
    "- DAYOFMONTH - returns an integer between 1 and 31 that represents the day of the argument\n",
    "- FIRST_DAY - returns a date or timestamp that represents the first day of the month of the argument\n",
    "- DAYS_TO_END_OF_MONTH - returns the number of days to the end of the month\n",
    "\n",
    "This is the current date so that you know what all of the calculations are based on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql VALUES NOW"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This expression (DAYOFMONTH) returns the day of the month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql VALUES DAYOFMONTH(NOW)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "FIRST_DAY will return the first day of the month. You could probably compute this with standard SQL date functions, but it is a lot easier just to use this builtin function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql VALUES FIRST_DAY(NOW)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, DAYS_TO_END_OF_MOTNH will return the number of days to the end of the month. A Zero would be returned if you are on the last day of the month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql VALUES DAYS_TO_END_OF_MONTH(NOW)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='add'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Date Addition Functions\n",
    "\n",
    "The date addition functions will add or subtract days from a current timestamp. The functions that \n",
    "are available are:\n",
    "\n",
    "- ADD_YEARS - Add years to a date\n",
    "- ADD_MONTHS - Add months to a date\n",
    "- ADD_DAYS - Add days to a date\n",
    "- ADD_HOURS - Add hours to a date\n",
    "- ADD_MINUTES - Add minutes to a date\n",
    "- ADD_SECONDS - Add seconds to a date\n",
    "\n",
    "The format of the function is:\n",
    "```Python \n",
    "ADD_DAYS ( expression, numeric expression )\n",
    "```\n",
    "\n",
    "The following SQL will add one \"unit\" to the current date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH DATES(FUNCTION, RESULT) AS \n",
    "  ( \n",
    "  VALUES\n",
    "    ('CURRENT DATE   ',NOW),\n",
    "    ('ADD_YEARS      ',ADD_YEARS(NOW,1)),\n",
    "    ('ADD_MONTHS     ',ADD_MONTHS(NOW,1)),\n",
    "    ('ADD_DAYS       ',ADD_DAYS(NOW,1)),\n",
    "    ('ADD_HOURS      ',ADD_HOURS(NOW,1)),\n",
    "    ('ADD_MINUTES    ',ADD_MINUTES(NOW,1)),\n",
    "    ('ADD_SECONDS    ',ADD_SECONDS(NOW,1))\n",
    "  )\n",
    "SELECT * FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A negative number can be used to subtract values from the current date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH DATES(FUNCTION, RESULT) AS \n",
    "  ( \n",
    "  VALUES\n",
    "    ('CURRENT DATE   ',NOW),\n",
    "    ('ADD_YEARS      ',ADD_YEARS(NOW,-1)),\n",
    "    ('ADD_MONTHS     ',ADD_MONTHS(NOW,-1)),\n",
    "    ('ADD_DAYS       ',ADD_DAYS(NOW,-1)),\n",
    "    ('ADD_HOURS      ',ADD_HOURS(NOW,-1)),\n",
    "    ('ADD_MINUTES    ',ADD_MINUTES(NOW,-1)),\n",
    "    ('ADD_SECONDS    ',ADD_SECONDS(NOW,-1))\n",
    "  )\n",
    "SELECT * FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='extract'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extracting Weeks, Months, Quarters, and Years from a Date\n",
    "\n",
    "There are four functions that extract different values from a date. These functions include:\n",
    "\n",
    "- THIS_QUARTER - returns the first day of the quarter\n",
    "- THIS_WEEK - returns the first day of the week (Sunday is considered the first day of that week)\n",
    "- THIS_MONTH - returns the first day of the month\n",
    "- THIS_YEAR - returns the first day of the year\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH DATES(FUNCTION, RESULT) AS \n",
    "  ( \n",
    "  VALUES\n",
    "    ('CURRENT DATE   ',NOW),\n",
    "    ('THIS_WEEK      ',THIS_WEEK(NOW)),\n",
    "    ('THIS_MONTH     ',THIS_MONTH(NOW)),\n",
    "    ('THIS_QUARTER   ',THIS_QUARTER(NOW)),\n",
    "    ('THIS_YEAR      ',THIS_YEAR(NOW))\n",
    "  )\n",
    "SELECT * FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is also a NEXT function for each of these. The NEXT function will return the next week, month, quarter,\n",
    "or year given a current date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH DATES(FUNCTION, RESULT) AS \n",
    "  ( \n",
    "  VALUES\n",
    "    ('CURRENT DATE   ',NOW),\n",
    "    ('NEXT_WEEK      ',NEXT_WEEK(NOW)),\n",
    "    ('NEXT_MONTH     ',NEXT_MONTH(NOW)),\n",
    "    ('NEXT_QUARTER   ',NEXT_QUARTER(NOW)),\n",
    "    ('NEXT_YEAR      ',NEXT_YEAR(NOW))\n",
    "  )\n",
    "SELECT * FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='nextday'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next Day Function\n",
    "\n",
    "The previous set of functions returned a date value for the current week, month, quarter, or year (or the next one\n",
    "if you used the NEXT function). The NEXT_DAY function returns the next day (after the date you supply) \n",
    "based on the string representation of the day. The date string will be dependent on the codepage that you are using for the database.\n",
    "\n",
    "The date (from an English perspective) can be:\n",
    "\n",
    "|Day       |Short form\n",
    "|:-------- |:---------\n",
    "|Monday    |MON\n",
    "|Tuesday   |TUE\n",
    "|Wednesday |WED\n",
    "|Thursday  |THU\n",
    "|Friday    |FRI\n",
    "|Saturday  |SAT\n",
    "|Sunday    |SUN\n",
    "\n",
    "The following SQL will show you the \"day\" after the current date that is Monday through Sunday."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH DATES(FUNCTION, RESULT) AS \n",
    "  ( \n",
    "  VALUES\n",
    "    ('CURRENT DATE   ',NOW),\n",
    "    ('Monday         ',NEXT_DAY(NOW,'Monday')),\n",
    "    ('Tuesday        ',NEXT_DAY(NOW,'TUE')),\n",
    "    ('Wednesday      ',NEXT_DAY(NOW,'Wednesday')),\n",
    "    ('Thursday       ',NEXT_DAY(NOW,'Thursday')),\n",
    "    ('Friday         ',NEXT_DAY(NOW,'FRI')),\n",
    "    ('Saturday       ',NEXT_DAY(NOW,'Saturday')),\n",
    "    ('Sunday         ',NEXT_DAY(NOW,'Sunday'))    \n",
    "  )\n",
    "SELECT * FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='between'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Between Date/Time Functions\n",
    "\n",
    "These date functions compute the number of full seconds, minutes, hours, days, weeks, and years between\n",
    "two dates. If there isn't a full value between the two objects (like less than a day), a zero will be\n",
    "returned. These new functions are:\n",
    "\n",
    "- HOURS_BETWEEN - returns the number of full hours between two arguments\n",
    "- MINUTES_BETWEEN - returns the number of full minutes between two arguments\n",
    "- SECONDS_BETWEEN - returns the number of full seconds between two arguments\n",
    "- DAYS_BETWEEN - returns the number of full days between two arguments\n",
    "- WEEKS_BETWEEN - returns the number of full weeks between two arguments\n",
    "- YEARS_BETWEEN - returns the number of full years between two arguments\n",
    "\n",
    "The format of the function is:\n",
    "```Python\n",
    "DAYS_BETWEEN( expression1, expression2 )\n",
    "```\n",
    "The following SQL will use a date that is in the future with exactly one extra second, minute, hour, day,\n",
    "week and year added to it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP VARIABLE FUTURE_DATE;\n",
    "CREATE VARIABLE FUTURE_DATE TIMESTAMP DEFAULT(NOW + 1 SECOND + 1 MINUTE + 1 HOUR + 8 DAYS + 1 YEAR);\n",
    "\n",
    "WITH DATES(FUNCTION, RESULT) AS (\n",
    "  VALUES         \n",
    "    ('SECONDS_BETWEEN',SECONDS_BETWEEN(FUTURE_DATE,NOW)),\n",
    "    ('MINUTES_BETWEEN',MINUTES_BETWEEN(FUTURE_DATE,NOW)),    \n",
    "    ('HOURS_BETWEEN  ',HOURS_BETWEEN(FUTURE_DATE,NOW)), \n",
    "    ('DAYS BETWEEN   ',DAYS_BETWEEN(FUTURE_DATE,NOW)),   \n",
    "    ('WEEKS_BETWEEN  ',WEEKS_BETWEEN(FUTURE_DATE,NOW)),   \n",
    "    ('YEARS_BETWEEN  ',YEARS_BETWEEN(FUTURE_DATE,NOW))\n",
    "  )\n",
    "SELECT * FROM DATES;  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='mbetween'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MONTHS_BETWEEN Function\n",
    "\n",
    "You may have noticed that the MONTHS_BETWEEN function was not in the previous list of functions. The\n",
    "reason for this is that the value returned for MONTHS_BETWEEN is different from the other functions. The MONTHS_BETWEEN\n",
    "function returns a DECIMAL value rather than an integer value. The reason for this is that the duration of a\n",
    "month is not as precise as a day, week or year. The following example will show how the duration is \n",
    "a decimal value rather than an integer. You could always truncate the value if you want an integer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH DATES(FUNCTION, RESULT) AS (\n",
    "  VALUES         \n",
    "    ('0 MONTH        ',MONTHS_BETWEEN(NOW, NOW)),\n",
    "    ('1 MONTH        ',MONTHS_BETWEEN(NOW + 1 MONTH, NOW)),    \n",
    "    ('1 MONTH + 1 DAY',MONTHS_BETWEEN(NOW + 1 MONTH + 1 DAY, NOW)), \n",
    "    ('LEAP YEAR      ',MONTHS_BETWEEN('2016-02-01','2016-03-01')),\n",
    "    ('NON-LEAP YEAR  ',MONTHS_BETWEEN('2015-02-01','2015-03-01'))\n",
    "  )\n",
    "SELECT * FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='duration'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Date Duration Functions\n",
    "\n",
    "An alternate way of representing date durations is through the use of an integer with the format YYYYMMDD where\n",
    "the YYYY represents the year, MM for the month and DD for the day. Date durations are easier to manipulate than\n",
    "timestamp values and take up substantially less storage.\n",
    "\n",
    "There are two new functions.\n",
    "\n",
    " - YMD_BETWEEN returns a numeric value that specifies the number of full years, full months, and full days between two datetime values\n",
    " - AGE returns a numeric value that represents the number of full years, full months, and full days between the current timestamp and the argument\n",
    "   \n",
    "This SQL statement will return various AGE calculations based on the current timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH DATES(FUNCTION, RESULT) AS (\n",
    "  VALUES         \n",
    "    ('AGE + 1 DAY                   ',AGE(NOW - 1 DAY)),\n",
    "    ('AGE + 1 MONTH                 ',AGE(NOW - 1 MONTH)),\n",
    "    ('AGE + 1 YEAR                  ',AGE(NOW - 1 YEAR)),\n",
    "    ('AGE + 1 DAY + 1 MONTH         ',AGE(NOW - 1 DAY - 1 MONTH)),\n",
    "    ('AGE + 1 DAY + 1 YEAR          ',AGE(NOW - 1 DAY - 1 YEAR)),\n",
    "    ('AGE + 1 DAY + 1 MONTH + 1 YEAR',AGE(NOW - 1 DAY - 1 MONTH - 1 YEAR))\n",
    "  )\n",
    "SELECT * FROM DATES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The YMD_BETWEEN function is similar to the AGE function except that it takes two date arguments. We can\n",
    "simulate the AGE function by supplying the NOW function to the YMD_BETWEEN function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "WITH DATES(FUNCTION, RESULT) AS (\n",
    "  VALUES         \n",
    "    ('1 DAY                   ',YMD_BETWEEN(NOW,NOW - 1 DAY)),\n",
    "    ('1 MONTH                 ',YMD_BETWEEN(NOW,NOW - 1 MONTH)),\n",
    "    ('1 YEAR                  ',YMD_BETWEEN(NOW,NOW - 1 YEAR)),\n",
    "    ('1 DAY + 1 MONTH         ',YMD_BETWEEN(NOW,NOW - 1 DAY - 1 MONTH)),\n",
    "    ('1 DAY + 1 YEAR          ',YMD_BETWEEN(NOW,NOW - 1 DAY - 1 YEAR)),\n",
    "    ('1 DAY + 1 MONTH + 1 YEAR',YMD_BETWEEN(NOW,NOW - 1 DAY - 1 MONTH - 1 YEAR))\n",
    "  )\n",
    "SELECT * FROM DATES "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='overlaps'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## OVERLAPS Predicate\n",
    "\n",
    "The OVERLAPS predicate is used to determine whether two chronological periods overlap. This is not a \n",
    "function within DB2, but rather a special SQL syntax extension. \n",
    "\n",
    "A chronological period is specified by a pair of date-time expressions. The first expression specifies\n",
    "the start of a period; the second specifies its end.\n",
    "\n",
    "```Python\n",
    "(start1,end1) OVERLAPS (start2, end2)\n",
    "```\n",
    "\n",
    "The beginning and end values are not included in the periods. The following \n",
    "summarizes the overlap logic. For example, the periods 2016-10-19 to 2016-10-20 \n",
    "and 2016-10-20 to 2016-10-21 do not overlap. \n",
    "   \n",
    "For instance, the following interval does not overlap."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES\n",
    "  CASE\n",
    "    WHEN \n",
    "      (NOW, NOW + 1 DAY) OVERLAPS (NOW + 1 DAY, NOW + 2 DAYS) THEN 'Overlaps'\n",
    "    ELSE\n",
    "      'No Overlap'\n",
    "  END"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the first date range is extended by one day then the range will overlap."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES\n",
    "  CASE\n",
    "    WHEN \n",
    "      (NOW, NOW + 2 DAYS) OVERLAPS (NOW + 1 DAY, NOW + 2 DAYS) THEN 'Overlaps'\n",
    "    ELSE\n",
    "      'No Overlap'\n",
    "  END"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Identical date ranges will overlap."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES\n",
    "  CASE\n",
    "    WHEN \n",
    "      (NOW, NOW + 1 DAY) OVERLAPS (NOW, NOW + 1 DAY) THEN 'Overlaps'\n",
    "    ELSE\n",
    "      'No Overlap'\n",
    "  END"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)\n",
    "<a id='utc'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UTC Time Conversions\n",
    "\n",
    "Db2 has two functions that allow you to translate timestamps to and from UTC (Coordinated Universal Time).\n",
    "\n",
    "The FROM_UTC_TIMESTAMP scalar function returns a TIMESTAMP that is converted from Coordinated Universal Time \n",
    "to the time zone specified by the time zone string. \n",
    "\n",
    "The TO_UTC_TIMESTAMP scalar function returns a TIMESTAMP that is converted to Coordinated Universal Time \n",
    "from the timezone that is specified by the timezone string. \n",
    "\n",
    "The format of the two functions is:\n",
    "\n",
    "```Python\n",
    "FROM_UTC_TIMESTAMP( expression, timezone )\n",
    "TO_UTC_TIMESTAMP( expression, timezone)\n",
    "```\n",
    "\n",
    "The return value from each of these functions is a timestamp. The \"expression\" is a timestamp that\n",
    "you want to convert to the local timezone (or convert to UTC). The timezone is \n",
    "an expression that specifies the time zone that the expression is to be adjusted to. \n",
    "The value of the timezone-expression must be a time zone name from the Internet Assigned Numbers Authority (IANA)\n",
    "time zone database. The standard format for a time zone name in the IANA database is Area/Location, where:\n",
    "\n",
    "- Area is the English name of a continent, ocean, or the special area 'Etc'\n",
    "- Location is the English name of a location within the area; usually a city, or small island\n",
    "\n",
    "Examples:\n",
    "\n",
    "- \"America/Toronto\"\n",
    "- \"Asia/Sakhalin\"\n",
    "- \"Etc/UTC\" (which represents Coordinated Universal Time)\n",
    "\n",
    "For complete details on the valid set of time zone names and the rules that are associated with those time zones,\n",
    "refer to the IANA time zone database. The database server uses version 2010c of the IANA time zone database. \n",
    "\n",
    "The result is a timestamp, adjusted from/to the Coordinated Universal Time time zone to the time zone \n",
    "specified by the timezone-expression. If the timezone-expression returns a value that is not a time zone \n",
    "in the IANA time zone database, then the value of expression is returned without being adjusted.\n",
    "\n",
    "The timestamp adjustment is done by first applying the raw offset from Coordinated Universal Time of the \n",
    "timezone-expression. If Daylight Saving Time is in effect at the adjusted timestamp for the time zone \n",
    "that is specified by the timezone-expression, then the Daylight Saving Time offset is also applied \n",
    "to the timestamp.\n",
    "\n",
    "Time zones that use Daylight Saving Time have ambiguities at the transition dates. When a time zone \n",
    "changes from standard time to Daylight Saving Time, a range of time does not occur as it is skipped \n",
    "during the transition. When a time zone changes from Daylight Saving Time to standard time, \n",
    "a range of time occurs twice. Ambiguous timestamps are treated as if they occurred when standard time \n",
    "was in effect for the time zone.    \n",
    "    \n",
    "Convert the Coordinated Universal Time timestamp '2011-12-25 09:00:00.123456' to the 'Asia/Tokyo' time zone. \n",
    "The following returns a TIMESTAMP with the value '2011-12-25 18:00:00.123456'.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES FROM_UTC_TIMESTAMP(TIMESTAMP '2011-12-25 09:00:00.123456', 'Asia/Tokyo');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert the Coordinated Universal Time timestamp '2014-11-02 06:55:00' to the 'America/Toronto' time zone. \n",
    "The following returns a TIMESTAMP with the value '2014-11-02 01:55:00'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES FROM_UTC_TIMESTAMP(TIMESTAMP'2014-11-02 06:55:00', 'America/Toronto');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert the Coordinated Universal Time timestamp '2015-03-02 06:05:00' to the 'America/Toronto' \n",
    "time zone. The following returns a TIMESTAMP with the value '2015-03-02 01:05:00'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES FROM_UTC_TIMESTAMP(TIMESTAMP'2015-03-02 06:05:00', 'America/Toronto');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert the timestamp '1970-01-01 00:00:00' to the Coordinated Universal Time timezone from the 'America/Denver'\n",
    "timezone. The following returns a TIMESTAMP with the value '1970-01-01 07:00:00'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "VALUES TO_UTC_TIMESTAMP(TIMESTAMP'1970-01-01 00:00:00', 'America/Denver');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using UTC Functions\n",
    "\n",
    "One of the applications for using the UTC is to take the transaction timestamp and normalize it across\n",
    "all systems that access the data. You can convert the timestamp to UTC on insert and then when it is \n",
    "retrieved, it can be converted to the local timezone.\n",
    "\n",
    "This example will use a number of techniques to hide the complexity of changing timestamps to local timezones.\n",
    "\n",
    "The following SQL will create our base transaction table (TXS_BASE) that will be used throughout the\n",
    "example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql -q\n",
    "DROP TABLE TXS_BASE;\n",
    "CREATE TABLE TXS_BASE\n",
    "  (\n",
    "  ID INTEGER NOT NULL,\n",
    "  CUSTID INTEGER NOT NULL,\n",
    "  TXTIME_UTC TIMESTAMP NOT NULL\n",
    "  );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The UTC functions will be written to take advantage of a local timezone variable called TIME_ZONE. This\n",
    "variable will contain the timezone of the server (or user) that is running the transaction. In this \n",
    "case we are using the timezone in Toronto, Canada."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE OR REPLACE VARIABLE TIME_ZONE VARCHAR(255) DEFAULT('America/Toronto');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The SET Command can be used to update the TIME_ZONE to the current location we are in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SET TIME_ZONE = 'America/Toronto'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to retrieve the value of the current timezone, we take advantage of a simple user-defined function\n",
    "called GET_TIMEZONE. It just retrieves the contents of the current TIME_ZONE variable that we set up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE OR REPLACE FUNCTION GET_TIMEZONE()\n",
    "  RETURNS VARCHAR(255)\n",
    "LANGUAGE SQL CONTAINS SQL\n",
    "  RETURN (TIME_ZONE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The TXS view is used by all SQL statements rather than the TXS_BASE table. The reason for this is to \n",
    "take advantage of INSTEAD OF triggers that can manipulate the UTC without modifying the original SQL.\n",
    "\n",
    "Note that when the data is returned from the view that the TXTIME field is converted from UTC to the current\n",
    "TIMEZONE that we are in. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql  \n",
    "CREATE OR REPLACE VIEW TXS AS\n",
    "  (\n",
    "  SELECT\n",
    "     ID,\n",
    "     CUSTID,\n",
    "     FROM_UTC_TIMESTAMP(TXTIME_UTC,GET_TIMEZONE()) AS TXTIME\n",
    "  FROM\n",
    "     TXS_BASE\n",
    "  )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An INSTEAD OF trigger (INSERT, UPDATE, and DELETE) is created against the TXS view so that any insert or \n",
    "update on a TXTIME column will be converted back to the UTC value. From an application perspective, \n",
    "we are using the local time, not the UTC time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql -d\n",
    "CREATE OR REPLACE TRIGGER I_TXS\n",
    "  INSTEAD OF INSERT ON TXS\n",
    "  REFERENCING NEW AS NEW_TXS\n",
    "  FOR EACH ROW MODE DB2SQL\n",
    "BEGIN ATOMIC\n",
    "  INSERT INTO TXS_BASE VALUES (\n",
    "     NEW_TXS.ID,\n",
    "     NEW_TXS.CUSTID,\n",
    "     TO_UTC_TIMESTAMP(NEW_TXS.TXTIME,GET_TIMEZONE())\n",
    "     );\n",
    "END\n",
    "@\n",
    "\n",
    "CREATE OR REPLACE TRIGGER U_TXS\n",
    "  INSTEAD OF UPDATE ON TXS\n",
    "  REFERENCING NEW AS NEW_TXS OLD AS OLD_TXS\n",
    "  FOR EACH ROW MODE DB2SQL\n",
    "BEGIN ATOMIC\n",
    "  UPDATE TXS_BASE \n",
    "     SET (ID, CUSTID, TXTIME_UTC) = \n",
    "         (NEW_TXS.ID,\n",
    "          NEW_TXS.CUSTID,\n",
    "          TO_UTC_TIMESTAMP(NEW_TXS.TXTIME,TIME_ZONE)\n",
    "          )\n",
    "     WHERE \n",
    "       TXS_BASE.ID = OLD_TXS.ID\n",
    "     ;\n",
    "END\n",
    "@\n",
    "\n",
    "CREATE OR REPLACE TRIGGER D_TXS\n",
    "  INSTEAD OF DELETE ON TXS\n",
    "  REFERENCING OLD AS OLD_TXS\n",
    "  FOR EACH ROW MODE DB2SQL\n",
    "BEGIN ATOMIC\n",
    "  DELETE FROM TXS_BASE \n",
    "     WHERE \n",
    "       TXS_BASE.ID = OLD_TXS.ID\n",
    "     ;\n",
    "END\n",
    "@"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point in time(!) we can start inserting records into our table. We have already set the timezone\n",
    "to be Toronto, so the next insert statement will take the current time (NOW) and insert it into the table. \n",
    "For reference, here is the current time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql VALUES NOW"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will insert one record into the table and immediately retrieve the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO TXS VALUES(1,1,NOW);\n",
    "\n",
    "SELECT * FROM TXS;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the timsstamp appears to be the same as what we insert (plus or minus a few seconds). What actually\n",
    "sits in the base table is the UTC time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM TXS_BASE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can modify the time that is returned to us by changing our local timezone. The statement will make \n",
    "the system think we are in Vancouver."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SET TIME_ZONE = 'America/Vancouver'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Retrieving the results will show that the timestamp has shifted by 3 hours (Vancouver is 3 hours behind\n",
    "Toronto)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM TXS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So what happens if we insert a record into the table now that we are in Vancouver?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO TXS VALUES(2,2,NOW);\n",
    "SELECT * FROM TXS;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data retrieved reflects the fact that we are now in Vancouver from an application perspective. Looking at the\n",
    "base table and you will see that everything has been converted to UTC time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM TXS_BASE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we can switch back to Toronto time and see when the transactions were done. You will see that from a\n",
    "Toronto perspetive tht the transactions were done three hours later because of the timezone differences."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SET TIME_ZONE = 'America/Toronto';\n",
    "SELECT * FROM TXS;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Back to Top](#top)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "#### Credits: IBM 2018, George Baklarz [baklarz@ca.ibm.com]"
   ]
  }
 ],
 "metadata": {
  "hide_code_all_hidden": false,
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}