{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PER-* and INST-* period types\n",
    "\n",
    "HEC DSS uses a different convention than most date time libraries in use. The one that is difficult to deal with is the the end of interval labeling\n",
    "\n",
    "For example, the monthly data in DSS is stored at the end of the month. Lets take a look\n",
    "\n",
    "The monthly value for January 1990 is stored as the datetime of 31JAN1990 2400 which first off all cannot be parsed as such by python libraries. If you take the time as stored (long) and convert it to datetime in python it will be displayed as 01FEB1990 0000. \n",
    "\n",
    "The last time stamp that is considered to belong to the month of January is 31JAN1990 2359.9999... to nano-second precision. However the timestamp stored in DSS files is actually the stroke of midnight of the end of January and python datetime libraries mark that as a February date.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-07T18:49:47.989816Z",
     "start_time": "2021-12-07T18:49:47.165992Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-07T18:50:06.569177Z",
     "start_time": "2021-12-07T18:50:06.350602Z"
    }
   },
   "outputs": [
    {
     "ename": "ParserError",
     "evalue": "hour must be in 0..23: 31JAN1990 2400",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\dateutil\\parser\\_parser.py\u001b[0m in \u001b[0;36mparse\u001b[1;34m(self, timestr, default, ignoretz, tzinfos, **kwargs)\u001b[0m\n\u001b[0;32m    648\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 649\u001b[1;33m             \u001b[0mret\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_build_naive\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdefault\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    650\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mValueError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\dateutil\\parser\\_parser.py\u001b[0m in \u001b[0;36m_build_naive\u001b[1;34m(self, res, default)\u001b[0m\n\u001b[0;32m   1234\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1235\u001b[1;33m         \u001b[0mnaive\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdefault\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mrepl\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1236\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: hour must be in 0..23",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mParserError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\_libs\\tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\_libs\\tslibs\\parsing.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslibs.parsing.parse_datetime_string\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\dateutil\\parser\\_parser.py\u001b[0m in \u001b[0;36mparse\u001b[1;34m(timestr, parserinfo, **kwargs)\u001b[0m\n\u001b[0;32m   1367\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1368\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mDEFAULTPARSER\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mparse\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtimestr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1369\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\dateutil\\parser\\_parser.py\u001b[0m in \u001b[0;36mparse\u001b[1;34m(self, timestr, default, ignoretz, tzinfos, **kwargs)\u001b[0m\n\u001b[0;32m    650\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mValueError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 651\u001b[1;33m             \u001b[0msix\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_from\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mParserError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;34m\": %s\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtimestr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    652\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\six.py\u001b[0m in \u001b[0;36mraise_from\u001b[1;34m(value, from_value)\u001b[0m\n",
      "\u001b[1;31mParserError\u001b[0m: hour must be in 0..23: 31JAN1990 2400",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\_libs\\tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: invalid string coercion to datetime",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\dateutil\\parser\\_parser.py\u001b[0m in \u001b[0;36mparse\u001b[1;34m(self, timestr, default, ignoretz, tzinfos, **kwargs)\u001b[0m\n\u001b[0;32m    648\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 649\u001b[1;33m             \u001b[0mret\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_build_naive\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdefault\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    650\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mValueError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\dateutil\\parser\\_parser.py\u001b[0m in \u001b[0;36m_build_naive\u001b[1;34m(self, res, default)\u001b[0m\n\u001b[0;32m   1234\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1235\u001b[1;33m         \u001b[0mnaive\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdefault\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mrepl\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1236\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: hour must be in 0..23",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mParserError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_9596/3106564415.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_datetime\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'31JAN1990 2400'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\core\\tools\\datetimes.py\u001b[0m in \u001b[0;36mto_datetime\u001b[1;34m(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache)\u001b[0m\n\u001b[0;32m    912\u001b[0m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconvert_listlike\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mformat\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    913\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 914\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconvert_listlike\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0marray\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mformat\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    915\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    916\u001b[0m     \u001b[1;31m#  error: Incompatible return value type (got \"Union[Timestamp, NaTType,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\core\\tools\\datetimes.py\u001b[0m in \u001b[0;36m_convert_listlike_datetimes\u001b[1;34m(arg, format, name, tz, unit, errors, infer_datetime_format, dayfirst, yearfirst, exact)\u001b[0m\n\u001b[0;32m    406\u001b[0m         \u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merrors\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    407\u001b[0m         \u001b[0mrequire_iso8601\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mrequire_iso8601\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 408\u001b[1;33m         \u001b[0mallow_object\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    409\u001b[0m     )\n\u001b[0;32m    410\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\core\\arrays\\datetimes.py\u001b[0m in \u001b[0;36mobjects_to_datetime64ns\u001b[1;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)\u001b[0m\n\u001b[0;32m   2191\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mvalues\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mview\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"i8\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtz_parsed\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2192\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mValueError\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mTypeError\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2193\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2194\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2195\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mtz_parsed\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\core\\arrays\\datetimes.py\u001b[0m in \u001b[0;36mobjects_to_datetime64ns\u001b[1;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)\u001b[0m\n\u001b[0;32m   2180\u001b[0m             \u001b[0myearfirst\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0myearfirst\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2181\u001b[0m             \u001b[0mrequire_iso8601\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mrequire_iso8601\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2182\u001b[1;33m             \u001b[0mallow_mixed\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mallow_mixed\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2183\u001b[0m         )\n\u001b[0;32m   2184\u001b[0m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreshape\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0morder\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0morder\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\_libs\\tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\_libs\\tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\_libs\\tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib._array_to_datetime_object\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\_libs\\tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib._array_to_datetime_object\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\pandas\\_libs\\tslibs\\parsing.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslibs.parsing.parse_datetime_string\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\dateutil\\parser\\_parser.py\u001b[0m in \u001b[0;36mparse\u001b[1;34m(timestr, parserinfo, **kwargs)\u001b[0m\n\u001b[0;32m   1366\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mparser\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mparserinfo\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mparse\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtimestr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1367\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1368\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mDEFAULTPARSER\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mparse\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtimestr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1369\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1370\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\dateutil\\parser\\_parser.py\u001b[0m in \u001b[0;36mparse\u001b[1;34m(self, timestr, default, ignoretz, tzinfos, **kwargs)\u001b[0m\n\u001b[0;32m    649\u001b[0m             \u001b[0mret\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_build_naive\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdefault\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    650\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mValueError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 651\u001b[1;33m             \u001b[0msix\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_from\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mParserError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;34m\": %s\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtimestr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    652\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    653\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mignoretz\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\Users\\psandhu\\Miniconda3\\envs\\dev_pyhecdss\\lib\\site-packages\\six.py\u001b[0m in \u001b[0;36mraise_from\u001b[1;34m(value, from_value)\u001b[0m\n",
      "\u001b[1;31mParserError\u001b[0m: hour must be in 0..23: 31JAN1990 2400"
     ]
    }
   ],
   "source": [
    "pd.to_datetime('31JAN1990 2400')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-07T18:51:36.555091Z",
     "start_time": "2021-12-07T18:51:36.539264Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('1990-01-31 00:00:00')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "last_day = pd.to_datetime('31JAN1990')\n",
    "last_day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See this discussion here https://stackoverflow.com/questions/37354105/find-the-end-of-the-month-of-a-pandas-dataframe-series on how to get end of month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-07T18:55:03.616837Z",
     "start_time": "2021-12-07T18:55:03.598915Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('1990-01-31 00:00:00')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas.tseries.offsets import MonthEnd\n",
    "last_day + MonthEnd(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "None of those will give 31JAN1990 2400 as the answer. If you force the issue, e.g. the last nano-second of the month you will get this"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-07T18:57:14.054896Z",
     "start_time": "2021-12-07T18:57:14.047927Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('1990-01-31 23:59:59.999999999')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "last_day + pd.offsets.MonthBegin() - pd.offsets.Nano()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-07T18:57:25.256734Z",
     "start_time": "2021-12-07T18:57:25.240805Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('1990-02-01 00:00:00')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime('01FEB1990 0000')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-07T20:03:28.135542Z",
     "start_time": "2021-12-07T20:03:28.120607Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PeriodIndex(['1990-01', '1990-02'], dtype='period[M]')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pr = pd.period_range(start='01JAN1990',periods=2,freq='M')\n",
    "pr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## So what is the answer to this dilemma?\n",
    "\n",
    "The best that can be done IMHO is to use the pandas PeriodIndex which signifies a range of time.\n",
    "E.g. the monthly data is read in with a period of 'M' and shifted by 1'M' interval to the left (-1) for data stored in DSS. \n",
    "\n",
    "This is not ideal because it uses the period_type 'PER-AVG' as the reason to shift the data and convert it to periods. \n",
    "\n",
    "Also math operations are not easily done with data indexed by PeriodIndex vs DatetimeIndex. These should be handled by users as it depends upon the usecase.\n",
    "\n",
    "One example would be to resample the datetimeindex'ed data to monthly and then add it to period indexed data. \n",
    "\n",
    "Another issue is that when resampling the user would want use different options so that the \n",
    "\n",
    "These issues are pandas issues and can be dealt with in the python or pandas space. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:dev_pyhecdss]",
   "language": "python",
   "name": "conda-env-dev_pyhecdss-py"
  },
  "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.7.11"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}