{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Date Manipulation Functions\n",
    "\n",
    "Let us go through some of the important date manipulation functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/7jgBUN9Mtww?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/7jgBUN9Mtww?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Getting Current Date and Timestamp\n",
    "* Date Arithmetic using `INTERVAL` and `-` operator\n",
    "* Getting beginning date or time using `date_trunc`\n",
    "* Extracting information using `to_char` as well as calendar functions.\n",
    "* Dealing with unix timestamp using `from_unixtime`, `to_unix_timestamp`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting Current Date and Timestamp\n",
    "\n",
    "Let us understand how to get the details about current or today's date as well as current timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/TD7kE_lHD4o?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/TD7kE_lHD4o?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n"
     ]
    }
   ],
   "source": [
    "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* `current_date` is the function or operator which will return today's date.\n",
    "* `current_timestamp` is the function or operator which will return current time up to milliseconds.\n",
    "* These are not like other functions and do not use **()** at the end.\n",
    "* There is a format associated with date and timestamp.\n",
    "  * Date - `yyyy-MM-dd`\n",
    "  * Timestamp - `yyyy-MM-dd HH:mm:ss.SSS`\n",
    "* We can apply all string manipulation functions on date or timestamp once they are typecasted to strings using `varchar`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2020, 12, 1),)]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_date AS current_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.250677+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 250677, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{note}\n",
    "Example of applying string manipulation functions on dates. However, it is not a good practice. Postgres provide functions on dates or timestamps for most of the common requirements.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2020',)]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT substring(current_date::varchar, 1, 4) AS current_date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Date Arithmetic\n",
    "Let us understand how to perform arithmetic on dates or timestamps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/kVUCr38f6wU?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/kVUCr38f6wU?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* We can add or subtract days or months or years from date or timestamp by using special operator called as `INTERVAL`.\n",
    "* We can also add or subtract hours, minutes, seconds etc from date or timestamp using `INTERVAL`.\n",
    "* We can combine multiple criteria in one operation using `INTERVAL`\n",
    "* We can get difference between 2 dates or timestamps using minus (`-`) operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n"
     ]
    }
   ],
   "source": [
    "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2021-01-02 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2021, 1, 2, 0, 0),)]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_date + INTERVAL '32 DAYS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2022-12-01 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2022, 12, 1, 0, 0),)]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_date + INTERVAL '730 DAYS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-12-02 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 12, 2, 0, 0),)]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_date + INTERVAL '-730 DAYS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-12-02 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 12, 2, 0, 0),)]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_date - INTERVAL '730 DAYS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2021-03-01 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2021, 3, 1, 0, 0),)]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_date + INTERVAL '3 MONTHS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2019-04-30 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2019, 4, 30, 0, 0),)]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT '2019-01-31'::date + INTERVAL '3 MONTHS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2019-05-03 03:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2019, 5, 3, 3, 0),)]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT '2019-01-31'::date + INTERVAL '3 MONTHS 3 DAYS 3 HOURS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2021-03-01 10:55:19.336241+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2021, 3, 1, 10, 55, 19, 336241, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp + INTERVAL '3 MONTHS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 20:55:19.343569+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 20, 55, 19, 343569, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp + INTERVAL '10 HOURS' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 11:05:19.350628+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 11, 5, 19, 350628, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp + INTERVAL '10 MINUTES' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 21:05:19.357712+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 21, 5, 19, 357712, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp + INTERVAL '10 HOURS 10 MINUTES' AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>454</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(454,)]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT '2019-03-30'::date - '2017-12-31'::date AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>-454</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(-454,)]"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT '2017-12-31'::date - '2019-03-30'::date AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>612</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(612,)]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_date - '2019-03-30'::date AS result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>612 days, 10:55:19.384205</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.timedelta(612, 39319, 384205),)]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp - '2019-03-30'::date AS result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Beginning Date or Time - date_trunc\n",
    "Let us understand how to use `date_trunc` on dates or timestamps and get beginning date or time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/Gc7-5Da2jlA?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/Gc7-5Da2jlA?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* We can use **MONTH** to get beginning date of the month.\n",
    "* **YEAR** can be used to get begining date of the year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n"
     ]
    }
   ],
   "source": [
    "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>year_beginning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-01-01 00:00:00+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 1, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT date_trunc('YEAR', current_date) AS year_beginning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>month_beginning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 00:00:00+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT date_trunc('MONTH', current_date) AS month_beginning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>week_beginning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-11-30 00:00:00+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 11, 30, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT date_trunc('WEEK', current_date) AS week_beginning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>day_beginning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 00:00:00+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT date_trunc('DAY', current_date) AS day_beginning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>hour_beginning</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:00:00+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT date_trunc('HOUR', current_timestamp) AS hour_beginning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extracting information using to_char\n",
    "\n",
    "Let us understand how to use `to_char` to extract information from date or timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/vdZhZYPESCs?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/vdZhZYPESCs?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is how we can get date related information such as year, month, day etc from date or timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n"
     ]
    }
   ],
   "source": [
    "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.457415+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 457415, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.463947+00:00</td>\n",
       "        <td>2020</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 463947, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '2020')]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'yyyy') AS year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.470978+00:00</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 470978, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '20')]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'yy') AS year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>month</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.477856+00:00</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 477856, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '12')]"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'MM') AS month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>day_of_month</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.485328+00:00</td>\n",
       "        <td>01</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 485328, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '01')]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'dd') AS day_of_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>day_of_month</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.492543+00:00</td>\n",
       "        <td>01</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 492543, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '01')]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'DD') AS day_of_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>day_of_year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.500876+00:00</td>\n",
       "        <td>336</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 500876, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '336')]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'DDD') AS day_of_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>month_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.508738+00:00</td>\n",
       "        <td>Dec</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 508738, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'Dec')]"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'Mon') AS month_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>month_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.516104+00:00</td>\n",
       "        <td>dec</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 516104, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'dec')]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'mon') AS month_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>month_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.524174+00:00</td>\n",
       "        <td>December </td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 524174, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'December ')]"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'Month') AS month_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>month_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.531890+00:00</td>\n",
       "        <td>december </td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 531890, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'december ')]"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'month') AS month_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>day_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.539086+00:00</td>\n",
       "        <td>tuesday  </td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 539086, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'tuesday  ')]"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'day') AS day_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>day_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.546707+00:00</td>\n",
       "        <td>TUE</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 546707, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'TUE')]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'DY') AS day_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{note}\n",
    "When we use `Day` to get the complete name of a day, it will return 9 character string by padding with spaces.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>dayname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.554521+00:00</td>\n",
       "        <td>Tuesday  </td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 554521, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'Tuesday  ')]"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'Day') AS dayname"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>dayname</th>\n",
       "        <th>dayname_length</th>\n",
       "        <th>dayname_trimmed_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.562443+00:00</td>\n",
       "        <td>Tuesday  </td>\n",
       "        <td>9</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 562443, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 'Tuesday  ', 9, 7)]"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char('2020-11-17'::date, 'Day') AS dayname,\n",
    "    length(to_char('2020-11-17'::date, 'Day')) AS dayname_length,\n",
    "    length(trim(to_char('2020-11-17'::date, 'Day'))) AS dayname_trimmed_length"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Here is how we can get time related information such as hour, minute, seconds, milliseconds etc from timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>hour24</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.569746+00:00</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 569746, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '10')]"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'HH') AS hour24"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>hour12</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.578703+00:00</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 578703, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '10')]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'hh') AS hour12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>minutes</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.588247+00:00</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 588247, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '12')]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'mm') AS minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>seconds</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.595061+00:00</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 595061, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '19')]"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'ss') AS seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_timestamp</th>\n",
       "        <th>millis</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-12-01 10:55:19.602141+00:00</td>\n",
       "        <td>602</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 12, 1, 10, 55, 19, 602141, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), '602')]"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT current_timestamp AS current_timestamp, \n",
    "    to_char(current_timestamp, 'MS') AS millis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Here is how we can get the information from date or timestamp in the format we require."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_month</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>202012</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('202012',)]"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT to_char(current_timestamp, 'yyyyMM') AS current_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20201201</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('20201201',)]"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT to_char(current_timestamp, 'yyyyMMdd') AS current_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020/12/01</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2020/12/01',)]"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT to_char(current_timestamp, 'yyyy/MM/dd') AS current_date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extracting information - extract\n",
    "\n",
    "We can get year, month, day etc from date or timestamp using `extract` function. For almost all these scenarios such as getting year, month, day etc we can use `to_char` as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/oD_BgxeW9JA?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/oD_BgxeW9JA?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Let us see the usage of `extract` to get information such as year, quarter, month, week, day, hour etc.\n",
    "* We can also use `date_part` in place of `extract`. However there is subtle difference between them with respect to the syntax."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n"
     ]
    }
   ],
   "source": [
    "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>century</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(21.0,)]"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(century FROM current_date) AS century"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>century</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(21.0,)]"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT date_part('century', current_date) AS century"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>decade</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>202.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(202.0,)]"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(decade FROM current_date) AS decade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>century</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>202.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(202.0,)]"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT date_part('decade', current_date) AS century"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2020.0,)]"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(year FROM current_date) AS year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>quarter</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4.0,)]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(quarter FROM current_date) AS quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>month</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12.0,)]"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(month FROM current_date) AS month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>week</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>49.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(49.0,)]"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(week FROM current_date) AS week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>day</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1.0,)]"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(day FROM current_date) AS day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>day_of_year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>336.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(336.0,)]"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(doy FROM current_date) AS day_of_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>day_of_week</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2.0,)]"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(dow FROM current_date) AS day_of_week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>hour</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10.0,)]"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(hour FROM current_timestamp) AS hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>minute</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>55.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(55.0,)]"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(minute FROM current_timestamp) AS minute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>second</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19.740129</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(19.740129,)]"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(second FROM current_timestamp) AS second"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>millis</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19747.729</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(19747.729,)]"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(milliseconds FROM current_timestamp) AS millis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dealing with Unix Timestamp\n",
    "\n",
    "Let us go through the functions that can be used to deal with Unix Timestamp."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/8I7S1PuJEO0?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/8I7S1PuJEO0?rel=0&amp;controls=1&amp;showinfo=0\" frameborder=\"0\" allowfullscreen></iframe>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* `extract` with `epoch` can be used to convert Unix epoch to regular timestamp. We can also use `date_part`;\n",
    "* `to_timestamp` can be used to convert timestamp to Unix epoch.\n",
    "* We can get Unix epoch or Unix timestamp by running `date '+%s'` in Unix/Linux terminal\n",
    "\n",
    "Let us sww how we can use functions such as `extract` or `to_timestamp` to convert between timestamp and Unix timestamp or epoch.\n",
    "\n",
    "* We can unix epoch in Unix/Linux terminal using `date '+%s'`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n"
     ]
    }
   ],
   "source": [
    "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>date_epoch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1606780800.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1606780800.0,)]"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(epoch FROM current_date) AS date_epoch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>date_epoch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1606780800.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1606780800.0,)]"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT date_part('epoch', current_date) AS date_epoch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>unixtime</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1556648331.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1556648331.0,)]"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT extract(epoch FROM '2019-04-30 18:18:51'::timestamp) AS unixtime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>time_from_epoch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2019-04-30 22:18:51+00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2019, 4, 30, 22, 18, 51, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)]"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT to_timestamp(1556662731) AS time_from_epoch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>time_from_epoch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2019-04-30</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2019, 4, 30),)]"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT to_timestamp(1556662731)::date AS time_from_epoch"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yyyymm_from_epoch</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201904</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(201904,)]"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT to_char(to_timestamp(1556662731), 'yyyyMM')::int AS yyyyMM_from_epoch"
   ]
  }
 ],
 "metadata": {
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}