Now using timestamps with timezones

diff --git a/complete.ipynb b/complete.ipynb
index 735b78f70fc97f202cfdcdd90b64d3cb12b106e0..ccbdaf02d1fa8e43df49d295c40bf12539e9cf57 100644 (file)
--- a/complete.ipynb
+++ b/complete.ipynb
@@ -2,7 +2,7 @@
  "cells": [
   {
    "cell_type": "code",
-   "execution_count": 1,
+   "execution_count": 31,
    "metadata": {
     "collapsed": true
    },
@@ -21,7 +21,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 2,
+   "execution_count": 32,
    "metadata": {
     "collapsed": true
    },
@@ -33,7 +33,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 3,
+   "execution_count": 33,
    "metadata": {
     "collapsed": true
    },
@@ -48,7 +48,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 4,
+   "execution_count": 34,
    "metadata": {
     "collapsed": true
    },
@@ -67,7 +67,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 5,
+   "execution_count": 35,
    "metadata": {
     "collapsed": true
    },
@@ -80,7 +80,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 6,
+   "execution_count": 36,
    "metadata": {
     "collapsed": true
    },
@@ -95,7 +95,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 7,
+   "execution_count": 37,
    "metadata": {
     "collapsed": true
    },
@@ -148,10 +148,10 @@
     "  'pg_type': 'inet'},\n",
     " './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',\n",
     "  'pg_table': 'reports',\n",
-    "  'pg_type': 'timestamp'},\n",
+    "  'pg_type': 'timestamptz'},\n",
     " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n",
     "  'pg_table': 'reports',\n",
-    "  'pg_type': 'timestamp'},\n",
+    "  'pg_type': 'timestamptz'},\n",
     " './report_metadata/email': {'pg_field_name': 'report_metadata_email',\n",
     "  'pg_table': 'reports',\n",
     "  'pg_type': 'varchar'},\n",
@@ -165,7 +165,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 8,
+   "execution_count": 38,
    "metadata": {
     "collapsed": true
    },
@@ -186,8 +186,12 @@
     "            field_names += [field_maps[f]['pg_field_name']]\n",
     "            if field_maps[f]['pg_type'] == 'int':\n",
     "                values[field_maps[f]['pg_field_name']] = int(report.find(fp).text)\n",
-    "            elif field_maps[f]['pg_type'] == 'timestamp':\n",
-    "                values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(fp).text))\n",
+    "            elif field_maps[f]['pg_type'] == 'timestamptz':\n",
+    "                # values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(fp).text))\n",
+    "                values[field_maps[f]['pg_field_name']] = \\\n",
+    "                    datetime.datetime.fromtimestamp(int(report.find(fp).text),  \n",
+    "                        # tz=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))\n",
+    "                                                    tz=datetime.timezone.utc)\n",
     "            elif field_maps[f]['pg_type'] == 'inet':\n",
     "                values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(fp).text)\n",
     "            else:\n",
@@ -205,7 +209,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 9,
+   "execution_count": 39,
    "metadata": {
     "collapsed": true
    },
@@ -234,61 +238,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 10,
-   "metadata": {
-    "collapsed": true
-   },
-   "outputs": [],
-   "source": [
-    "def write_report_old(connection, cursor, report):\n",
-    "    \n",
-    "    field_names = []\n",
-    "    values = {}\n",
-    "    for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'reports']:\n",
-    "        field_names += [field_maps[f]['pg_field_name']]\n",
-    "        if field_maps[f]['pg_type'] == 'int':\n",
-    "            values[field_maps[f]['pg_field_name']] = int(report.find(f).text)\n",
-    "        elif field_maps[f]['pg_type'] == 'timestamp':\n",
-    "            values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(f).text))\n",
-    "        elif field_maps[f]['pg_type'] == 'inet':\n",
-    "            values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f).text)\n",
-    "        else:\n",
-    "            values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f).text)\n",
-    "    insert_string = 'insert into reports (' + ', '.join(field_names) + ') '\n",
-    "    insert_string += 'values (' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n",
-    "    # print(insert_string, values)\n",
-    "    cursor.execute(insert_string, values)\n",
-    "    \n",
-    "    for i in range(1, len(report.findall('./record'))+1):\n",
-    "        field_names = []\n",
-    "        cursor.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n",
-    "            [report.find('./report_metadata/report_id').text])\n",
-    "        results = cursor.fetchall()\n",
-    "        if len(results) != 1:\n",
-    "            raise\n",
-    "        else:\n",
-    "            report_id = results[0][0]\n",
-    "        values = {'report_id': report_id}\n",
-    "        for f in [f for f in field_maps if field_maps[f]['pg_table'] == 'report_items']:\n",
-    "            field_names += [field_maps[f]['pg_field_name']]\n",
-    "            if field_maps[f]['pg_type'] == 'int':\n",
-    "                values[field_maps[f]['pg_field_name']] = int(report.find(f.format(i)).text)\n",
-    "            elif field_maps[f]['pg_type'] == 'timestamp':\n",
-    "                values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(report.find(f.format(i)).text))\n",
-    "            elif field_maps[f]['pg_type'] == 'inet':\n",
-    "                values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f.format(i)).text)\n",
-    "            else:\n",
-    "                values[field_maps[f]['pg_field_name']] = maybe_strip(report.find(f.format(i)).text)\n",
-    "        insert_string = 'insert into report_items (report_id, ' + ', '.join(field_names) + ') '\n",
-    "        insert_string += 'values (%(report_id)s, ' + ', '.join('%({})s'.format(f) for f in field_names) + ');'\n",
-    "        # print(insert_string, values)\n",
-    "        cursor.execute(insert_string, values)\n",
-    "    connection.commit()"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": 11,
+   "execution_count": 40,
    "metadata": {
     "collapsed": false
    },
@@ -299,7 +249,7 @@
        "['dmarc.ini']"
       ]
      },
-     "execution_count": 11,
+     "execution_count": 40,
      "metadata": {},
      "output_type": "execute_result"
     }
@@ -311,7 +261,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 12,
+   "execution_count": 41,
    "metadata": {
     "collapsed": false
    },
@@ -320,39 +270,27 @@
     "conn = psycopg2.connect(host=config['database']['server'],\n",
     "                        database=config['database']['database'], \n",
     "                        user=config['database']['username'], \n",
-    "                        password=config['database']['password'])"
+    "                        password=config['database']['password'])\n",
+    "cur = conn.cursor()"
    ]
   },
   {
    "cell_type": "code",
-   "execution_count": 13,
+   "execution_count": 42,
    "metadata": {
     "collapsed": false
    },
-   "outputs": [
-    {
-     "data": {
-      "text/plain": [
-       "datetime.datetime(2016, 3, 30, 23, 59, 59)"
-      ]
-     },
-     "execution_count": 13,
-     "metadata": {},
-     "output_type": "execute_result"
-    }
-   ],
+   "outputs": [],
    "source": [
-    "with conn:\n",
-    "    with conn.cursor() as cur:\n",
-    "        cur.execute('select max(report_metadata_date_range_end) from reports')\n",
-    "        results = cur.fetchall()\n",
+    "cur.execute('select max(report_metadata_date_range_end) from reports')\n",
+    "results = cur.fetchall()\n",
     "most_recent_date = results[0][0]\n",
     "most_recent_date"
    ]
   },
   {
    "cell_type": "code",
-   "execution_count": 14,
+   "execution_count": 43,
    "metadata": {
     "collapsed": false
    },
@@ -360,10 +298,10 @@
     {
      "data": {
       "text/plain": [
-       "('OK', [b'179'])"
+       "('OK', [b'182'])"
       ]
      },
-     "execution_count": 14,
+     "execution_count": 43,
      "metadata": {},
      "output_type": "execute_result"
     }
@@ -378,7 +316,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 15,
+   "execution_count": 44,
    "metadata": {
     "collapsed": false
    },
@@ -386,10 +324,12 @@
     {
      "data": {
       "text/plain": [
-       "('SINCE 28-Mar-2016', 'OK', [b'172 173 174 175 176 177 178 179 180'])"
+       "('ALL',\n",
+       " 'OK',\n",
+       " [b'2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183'])"
       ]
      },
-     "execution_count": 15,
+     "execution_count": 44,
      "metadata": {},
      "output_type": "execute_result"
     }
@@ -405,7 +345,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 16,
+   "execution_count": 45,
    "metadata": {
     "collapsed": false
    },
@@ -413,7 +353,177 @@
     {
      "data": {
       "text/plain": [
-       "['1459129809.695034',\n",
+       "['1448246712.259522',\n",
+       " '75eef2128eb84e9ca8e4837f3d4e31bd@hotmail.com',\n",
+       " '9138782308848375092',\n",
+       " '840fc02d5cb847ec9f007fb236c0c190@hotmail.com',\n",
+       " '15111277194568576101',\n",
+       " '1448419746.206772',\n",
+       " '1448592471.754446',\n",
+       " 'c8e4fe772ae3434594e8396a8abf77b1@hotmail.com',\n",
+       " '2273327349069127175',\n",
+       " '1448851661.602960',\n",
+       " '1448938177.97045',\n",
+       " '18203685646345145151',\n",
+       " '12700770160536483846',\n",
+       " '1449024342.601561',\n",
+       " '1fadaaf3d9d544568a41042a1f42df05@hotmail.com',\n",
+       " '3544432548848738700',\n",
+       " '1449110875.443288',\n",
+       " '1449197294.177690',\n",
+       " '10356602625290246518',\n",
+       " '1449283514.868026',\n",
+       " '4654132158287507198',\n",
+       " '5164714352250096219',\n",
+       " '1449542804.259788',\n",
+       " '20246ec4a70041caa276560032fdc595@hotmail.com',\n",
+       " '14089985608998676943',\n",
+       " '1449629119.120001',\n",
+       " 'c8f339f8f3364d78820dd03a84d11daa@hotmail.com',\n",
+       " '1233953228184721030',\n",
+       " '17984785070730076605',\n",
+       " '1449888489.668429',\n",
+       " '121bb39637c14847a40669dd3e7cdf49@hotmail.com',\n",
+       " '14661842628106423589',\n",
+       " '1449974899.615312',\n",
+       " '7834597727856283739',\n",
+       " 'f94f00dee713479fb8bfdbcad539da88@hotmail.com',\n",
+       " '17659080448126516391',\n",
+       " '1450234735.690828',\n",
+       " '17880573400612806781',\n",
+       " '1451184218.12383',\n",
+       " '6505353761044749597',\n",
+       " '02aca4c6bb144bb6b54773374c14a43a@hotmail.com',\n",
+       " '1451443783.692950',\n",
+       " '12137203242660163862',\n",
+       " 'd1019291012e4f4589f59f1dacef31a3@hotmail.com',\n",
+       " '1451616408.476341',\n",
+       " '11743474449139138027',\n",
+       " 'e245ef81f05b4697bea34bed977d8286@hotmail.com',\n",
+       " '1452134765.308922',\n",
+       " '271c80b9234a4c99bb9608abc5470c99@hotmail.com',\n",
+       " '15974729567081493290',\n",
+       " 'ba1e9c35eec5428b8c3d6a32d25daa46@hotmail.com',\n",
+       " '1452393953.83099',\n",
+       " 'd71792d8afc246e9a4756bd13e600b59@hotmail.com',\n",
+       " '1452480188.666644',\n",
+       " '1452566814.805501',\n",
+       " 'fc0750780e0d4b1395c4c9f41cb9791f@hotmail.com',\n",
+       " '1452653275.294149',\n",
+       " '1452741832.342075',\n",
+       " '2884439811958006165',\n",
+       " '3508955360705645678',\n",
+       " '1452826445.971145',\n",
+       " '1452912512.114840',\n",
+       " 'eda16c25bbdb48b2bae0d18eab13f736@hotmail.com',\n",
+       " '724976339745122723',\n",
+       " '1453084984.369330',\n",
+       " '1453171440.157305',\n",
+       " '1862812391966551302',\n",
+       " '1453258067.440890',\n",
+       " '1453344550.144698',\n",
+       " '2002148329698541727',\n",
+       " '6110291457768008366',\n",
+       " 'de651cefbc0749a3aa3d91cdc5084f5a@hotmail.com',\n",
+       " '1453517236.399019',\n",
+       " '14593873841710243963',\n",
+       " 'ba64911adb0743d0b926588647ec5dcf@hotmail.com',\n",
+       " '1453689981.768611',\n",
+       " '13959504937502303973',\n",
+       " '3eba8b9e31724809aaf8d517e5656c4b@hotmail.com',\n",
+       " '1453776417.898739',\n",
+       " '9594298247075547297',\n",
+       " 'd77c5b90344149f5bb7b50ff69400aeb@hotmail.com',\n",
+       " '1453862958.482897',\n",
+       " '3267388508667988463',\n",
+       " '580ca0a74948493ba8b7c6e62b8c81d9@hotmail.com',\n",
+       " '6876229717666715946',\n",
+       " '6474240574237085621',\n",
+       " '1454381343.228960',\n",
+       " '77b6c2aa32bf440aa240195db229cd4a@hotmail.com',\n",
+       " '1922265692629464594',\n",
+       " '1454467952.258012',\n",
+       " '1454640482.903992',\n",
+       " '16409503372348966326',\n",
+       " '456ffff78456494fbaed47b465259d46@hotmail.com',\n",
+       " '543258397004515473',\n",
+       " '4158049796460953654',\n",
+       " 'a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com',\n",
+       " '1454986282.636073',\n",
+       " '8986675713054039362',\n",
+       " '30d5ef78d6d746a4a777c56e7ca6f1c6@hotmail.com',\n",
+       " '1455072659.49250',\n",
+       " '10744929815548178249',\n",
+       " '1178c4fe34364ff39cb6e24a51219093@hotmail.com',\n",
+       " '730219275619457',\n",
+       " '164180405175632647',\n",
+       " '16520810497197721925',\n",
+       " '13f28e952d67451281b90d55eee9c7c3@hotmail.com',\n",
+       " '1455418081.446180',\n",
+       " '641b4cc500334a7eb3f649b21af003ca@hotmail.com',\n",
+       " '14416654486328871570',\n",
+       " 'dbe2f49dbea947428b179e0ecec5f813@hotmail.com',\n",
+       " '1455679107.469767',\n",
+       " '7815164892280952980',\n",
+       " '511d6fbb8484454b916ad8d799938390@hotmail.com',\n",
+       " '1455763965.140751',\n",
+       " '10491829640821461358',\n",
+       " '13702792868066092542',\n",
+       " '1456109267.60200',\n",
+       " '2cfe3d0ec75b43f89a6a7ae57777e25c@hotmail.com',\n",
+       " '9037150256193522538',\n",
+       " 'e327753089e04950906848d104a93695@hotmail.com',\n",
+       " '1456282325.455667',\n",
+       " '3584764172722215181',\n",
+       " '1456455410.661669',\n",
+       " '16824795799639390032',\n",
+       " '14217880258295310962',\n",
+       " '1456541727.873510',\n",
+       " '1102163566627474204',\n",
+       " '1456713880.905762',\n",
+       " '1456800606.884839',\n",
+       " '1bcc49be66744cc7a70ae1cc53515bbf@hotmail.com',\n",
+       " '11019573554877343650',\n",
+       " '1456886761.643377',\n",
+       " '36dd4e2b3f424bb6919d2d5d4c77c8dd@hotmail.com',\n",
+       " 'b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com',\n",
+       " '1457059537.625770',\n",
+       " '85136d21c514431e9f5cf9d36faa4a22@hotmail.com',\n",
+       " '1457146943.63077',\n",
+       " '1457232742.472142',\n",
+       " 'ea3a96df66a845c5a666068ef8ca1368@hotmail.com',\n",
+       " '17044038014166863505',\n",
+       " '1457318880.887129',\n",
+       " '3689737760348689620',\n",
+       " '726a3261dfab4b4590b5fc898c561b08@hotmail.com',\n",
+       " '129a9dc3ff2e40ae8a3890ecc1c291d9@hotmail.com',\n",
+       " '1457491692.793016',\n",
+       " '1207168150882246442',\n",
+       " '10706879170593875813',\n",
+       " '4226319448276355598',\n",
+       " '1457750956.253773',\n",
+       " '12434676377746978866',\n",
+       " '43e7b5e2e0fb400aa914058360c8aaab@hotmail.com',\n",
+       " '1457919850.989656',\n",
+       " '594749737641304465',\n",
+       " '18c9a966da81401e80ca2a22310ffee6@hotmail.com',\n",
+       " '1458006833.731671',\n",
+       " '6996084840178215735',\n",
+       " '1458093082.193350',\n",
+       " '3188090359734380692',\n",
+       " '7006513620611789399',\n",
+       " '1458353823.362308',\n",
+       " '17071564483265101388',\n",
+       " '1458524834.32161',\n",
+       " '1458611429.595569',\n",
+       " '14662396456930987863',\n",
+       " '1458784545.303847',\n",
+       " '13032699446207263761',\n",
+       " '8426152571808739514',\n",
+       " '1458957186.548175',\n",
+       " '2150510829392606201',\n",
+       " '68aad5080a774e2c997d159b546569b9@hotmail.com',\n",
+       " '1459129809.695034',\n",
        " '16143280651570354241',\n",
        " '8c177254c3cb41869dc3afab59f74c76@hotmail.com',\n",
        " '15410706527896810898',\n",
@@ -421,10 +531,13 @@
        " '15497495941279624940',\n",
        " '1459302353.261157',\n",
        " '7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com',\n",
-       " '15185964531645951164']"
+       " '15185964531645951164',\n",
+       " '1459391203.751219',\n",
+       " '9452413620101638402',\n",
+       " '1459475662.735732']"
       ]
      },
-     "execution_count": 16,
+     "execution_count": 45,
      "metadata": {},
      "output_type": "execute_result"
     }
@@ -437,7 +550,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 17,
+   "execution_count": 46,
    "metadata": {
     "collapsed": false
    },
@@ -448,7 +561,7 @@
        "('BYE', [b'Logging out'])"
       ]
      },
-     "execution_count": 17,
+     "execution_count": 46,
      "metadata": {},
      "output_type": "execute_result"
     }
@@ -460,26 +573,311 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 18,
+   "execution_count": 47,
    "metadata": {
     "collapsed": false
    },
+   "outputs": [
+    {
+     "name": "stdout",
+     "output_type": "stream",
+     "text": [
+      "write 1448246712.259522\n",
+      "write 75eef2128eb84e9ca8e4837f3d4e31bd@hotmail.com\n",
+      "write 9138782308848375092\n",
+      "write 840fc02d5cb847ec9f007fb236c0c190@hotmail.com\n",
+      "write 15111277194568576101\n",
+      "write 1448419746.206772\n",
+      "write 1448592471.754446\n",
+      "write c8e4fe772ae3434594e8396a8abf77b1@hotmail.com\n",
+      "write 2273327349069127175\n",
+      "write 1448851661.602960\n",
+      "write 1448938177.97045\n",
+      "write 18203685646345145151\n",
+      "write 12700770160536483846\n",
+      "write 1449024342.601561\n",
+      "write 1fadaaf3d9d544568a41042a1f42df05@hotmail.com\n",
+      "write 3544432548848738700\n",
+      "write 1449110875.443288\n",
+      "write 1449197294.177690\n",
+      "write 10356602625290246518\n",
+      "write 1449283514.868026\n",
+      "write 4654132158287507198\n",
+      "write 5164714352250096219\n",
+      "write 1449542804.259788\n",
+      "write 20246ec4a70041caa276560032fdc595@hotmail.com\n",
+      "write 14089985608998676943\n",
+      "write 1449629119.120001\n",
+      "write c8f339f8f3364d78820dd03a84d11daa@hotmail.com\n",
+      "write 1233953228184721030\n",
+      "write 17984785070730076605\n",
+      "write 1449888489.668429\n",
+      "write 121bb39637c14847a40669dd3e7cdf49@hotmail.com\n",
+      "write 14661842628106423589\n",
+      "write 1449974899.615312\n",
+      "write 7834597727856283739\n",
+      "write f94f00dee713479fb8bfdbcad539da88@hotmail.com\n",
+      "write 17659080448126516391\n",
+      "write 1450234735.690828\n",
+      "write 17880573400612806781\n",
+      "write 1451184218.12383\n",
+      "write 6505353761044749597\n",
+      "write 02aca4c6bb144bb6b54773374c14a43a@hotmail.com\n",
+      "write 1451443783.692950\n",
+      "write 12137203242660163862\n",
+      "write d1019291012e4f4589f59f1dacef31a3@hotmail.com\n",
+      "write 1451616408.476341\n",
+      "write 11743474449139138027\n",
+      "write e245ef81f05b4697bea34bed977d8286@hotmail.com\n",
+      "write 1452134765.308922\n",
+      "write 271c80b9234a4c99bb9608abc5470c99@hotmail.com\n",
+      "write 15974729567081493290\n",
+      "write ba1e9c35eec5428b8c3d6a32d25daa46@hotmail.com\n",
+      "write 1452393953.83099\n",
+      "write d71792d8afc246e9a4756bd13e600b59@hotmail.com\n",
+      "write 1452480188.666644\n",
+      "write 1452566814.805501\n",
+      "write fc0750780e0d4b1395c4c9f41cb9791f@hotmail.com\n",
+      "write 1452653275.294149\n",
+      "write 1452741832.342075\n",
+      "write 2884439811958006165\n",
+      "write 3508955360705645678\n",
+      "write 1452826445.971145\n",
+      "write 1452912512.114840\n",
+      "write eda16c25bbdb48b2bae0d18eab13f736@hotmail.com\n",
+      "write 724976339745122723\n",
+      "write 1453084984.369330\n",
+      "write 1453171440.157305\n",
+      "write 1862812391966551302\n",
+      "write 1453258067.440890\n",
+      "write 1453344550.144698\n",
+      "write 2002148329698541727\n",
+      "write 6110291457768008366\n",
+      "write de651cefbc0749a3aa3d91cdc5084f5a@hotmail.com\n",
+      "write 1453517236.399019\n",
+      "write 14593873841710243963\n",
+      "write ba64911adb0743d0b926588647ec5dcf@hotmail.com\n",
+      "write 1453689981.768611\n",
+      "write 13959504937502303973\n",
+      "write 3eba8b9e31724809aaf8d517e5656c4b@hotmail.com\n",
+      "write 1453776417.898739\n",
+      "write 9594298247075547297\n",
+      "write d77c5b90344149f5bb7b50ff69400aeb@hotmail.com\n",
+      "write 1453862958.482897\n",
+      "write 3267388508667988463\n",
+      "write 580ca0a74948493ba8b7c6e62b8c81d9@hotmail.com\n",
+      "write 6876229717666715946\n",
+      "write 6474240574237085621\n",
+      "write 1454381343.228960\n",
+      "write 77b6c2aa32bf440aa240195db229cd4a@hotmail.com\n",
+      "write 1922265692629464594\n",
+      "write 1454467952.258012\n",
+      "write 1454640482.903992\n",
+      "write 16409503372348966326\n",
+      "write 456ffff78456494fbaed47b465259d46@hotmail.com\n",
+      "write 543258397004515473\n",
+      "write 4158049796460953654\n",
+      "write a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com\n",
+      "write 1454986282.636073\n",
+      "write 8986675713054039362\n",
+      "write 30d5ef78d6d746a4a777c56e7ca6f1c6@hotmail.com\n",
+      "write 1455072659.49250\n",
+      "write 10744929815548178249\n",
+      "write 1178c4fe34364ff39cb6e24a51219093@hotmail.com\n",
+      "write 730219275619457\n",
+      "write 164180405175632647\n",
+      "write 16520810497197721925\n",
+      "write 13f28e952d67451281b90d55eee9c7c3@hotmail.com\n",
+      "write 1455418081.446180\n",
+      "write 641b4cc500334a7eb3f649b21af003ca@hotmail.com\n",
+      "write 14416654486328871570\n",
+      "write dbe2f49dbea947428b179e0ecec5f813@hotmail.com\n",
+      "write 1455679107.469767\n",
+      "write 7815164892280952980\n",
+      "write 511d6fbb8484454b916ad8d799938390@hotmail.com\n",
+      "write 1455763965.140751\n",
+      "write 10491829640821461358\n",
+      "write 13702792868066092542\n",
+      "write 1456109267.60200\n",
+      "write 2cfe3d0ec75b43f89a6a7ae57777e25c@hotmail.com\n",
+      "write 9037150256193522538\n",
+      "write e327753089e04950906848d104a93695@hotmail.com\n",
+      "write 1456282325.455667\n",
+      "write 3584764172722215181\n",
+      "write 1456455410.661669\n",
+      "write 16824795799639390032\n",
+      "write 14217880258295310962\n",
+      "write 1456541727.873510\n",
+      "write 1102163566627474204\n",
+      "write 1456713880.905762\n",
+      "write 1456800606.884839\n",
+      "write 1bcc49be66744cc7a70ae1cc53515bbf@hotmail.com\n",
+      "write 11019573554877343650\n",
+      "write 1456886761.643377\n",
+      "write 36dd4e2b3f424bb6919d2d5d4c77c8dd@hotmail.com\n",
+      "write b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com\n",
+      "write 1457059537.625770\n",
+      "write 85136d21c514431e9f5cf9d36faa4a22@hotmail.com\n",
+      "write 1457146943.63077\n",
+      "write 1457232742.472142\n",
+      "write ea3a96df66a845c5a666068ef8ca1368@hotmail.com\n",
+      "write 17044038014166863505\n",
+      "write 1457318880.887129\n",
+      "write 3689737760348689620\n",
+      "write 726a3261dfab4b4590b5fc898c561b08@hotmail.com\n",
+      "write 129a9dc3ff2e40ae8a3890ecc1c291d9@hotmail.com\n",
+      "write 1457491692.793016\n",
+      "write 1207168150882246442\n",
+      "write 10706879170593875813\n",
+      "write 4226319448276355598\n",
+      "write 1457750956.253773\n",
+      "write 12434676377746978866\n",
+      "write 43e7b5e2e0fb400aa914058360c8aaab@hotmail.com\n",
+      "write 1457919850.989656\n",
+      "write 594749737641304465\n",
+      "write 18c9a966da81401e80ca2a22310ffee6@hotmail.com\n",
+      "write 1458006833.731671\n",
+      "write 6996084840178215735\n",
+      "write 1458093082.193350\n",
+      "write 3188090359734380692\n",
+      "write 7006513620611789399\n",
+      "write 1458353823.362308\n",
+      "write 17071564483265101388\n",
+      "write 1458524834.32161\n",
+      "write 1458611429.595569\n",
+      "write 14662396456930987863\n",
+      "write 1458784545.303847\n",
+      "write 13032699446207263761\n",
+      "write 8426152571808739514\n",
+      "write 1458957186.548175\n",
+      "write 2150510829392606201\n",
+      "write 68aad5080a774e2c997d159b546569b9@hotmail.com\n",
+      "write 1459129809.695034\n",
+      "write 16143280651570354241\n",
+      "write 8c177254c3cb41869dc3afab59f74c76@hotmail.com\n",
+      "write 15410706527896810898\n",
+      "write 1459216304.582931\n",
+      "write 15497495941279624940\n",
+      "write 1459302353.261157\n",
+      "write 7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com\n",
+      "write 15185964531645951164\n",
+      "write 1459391203.751219\n",
+      "write 9452413620101638402\n",
+      "write 1459475662.735732\n"
+     ]
+    }
+   ],
+   "source": [
+    "for report in dmarc_reports:\n",
+    "    cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n",
+    "            [report.find('./report_metadata/report_id').text])\n",
+    "    results = cur.fetchall()\n",
+    "    if not results:\n",
+    "        print('write', report.find('./report_metadata/report_id').text)\n",
+    "        write_report(conn, cur, report)"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 48,
+   "metadata": {
+    "collapsed": true
+   },
    "outputs": [],
    "source": [
-    "with conn:\n",
-    "    with conn.cursor() as cur:\n",
-    "        for report in dmarc_reports:\n",
-    "            cur.execute('select id, report_metadata_report_id from reports where report_metadata_report_id = %s;', \n",
-    "                [report.find('./report_metadata/report_id').text])\n",
-    "            results = cur.fetchall()\n",
-    "            if not results:\n",
-    "                print('write', report.find('./report_metadata/report_id').text)\n",
-    "                write_report(conn, cur, report)"
+    "# conn.rollback()"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 52,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "('insert into reports (policy_published_adkim, report_metadata_org_name, policy_published_pct, policy_published_aspf, policy_published_domain, report_metadata_date_range_begin, report_metadata_report_id, report_metadata_email, policy_published_p, report_metadata_date_range_end) values (%(policy_published_adkim)s, %(report_metadata_org_name)s, %(policy_published_pct)s, %(policy_published_aspf)s, %(policy_published_domain)s, %(report_metadata_date_range_begin)s, %(report_metadata_report_id)s, %(report_metadata_email)s, %(policy_published_p)s, %(report_metadata_date_range_end)s);',\n",
+       " {'policy_published_adkim': 'r',\n",
+       "  'policy_published_aspf': 'r',\n",
+       "  'policy_published_domain': 'njae.me.uk',\n",
+       "  'policy_published_p': 'none',\n",
+       "  'policy_published_pct': 100,\n",
+       "  'report_metadata_date_range_begin': datetime.datetime(2015, 11, 22, 0, 0, tzinfo=datetime.timezone.utc),\n",
+       "  'report_metadata_date_range_end': datetime.datetime(2015, 11, 22, 23, 59, 59, tzinfo=datetime.timezone.utc),\n",
+       "  'report_metadata_email': 'postmaster@dmarc.yahoo.com',\n",
+       "  'report_metadata_org_name': 'Yahoo! Inc.',\n",
+       "  'report_metadata_report_id': '1448246712.259522'})"
+      ]
+     },
+     "execution_count": 52,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "build_insert_command('reports', dmarc_reports[0])"
    ]
   },
   {
    "cell_type": "code",
-   "execution_count": 19,
+   "execution_count": 53,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "('insert into reports (policy_published_adkim, report_metadata_org_name, policy_published_pct, policy_published_aspf, policy_published_domain, report_metadata_date_range_begin, report_metadata_report_id, report_metadata_email, policy_published_p, report_metadata_date_range_end) values (%(policy_published_adkim)s, %(report_metadata_org_name)s, %(policy_published_pct)s, %(policy_published_aspf)s, %(policy_published_domain)s, %(report_metadata_date_range_begin)s, %(report_metadata_report_id)s, %(report_metadata_email)s, %(policy_published_p)s, %(report_metadata_date_range_end)s);',\n",
+       " {'policy_published_adkim': 'r',\n",
+       "  'policy_published_aspf': 'r',\n",
+       "  'policy_published_domain': 'njae.me.uk',\n",
+       "  'policy_published_p': 'none',\n",
+       "  'policy_published_pct': 100,\n",
+       "  'report_metadata_date_range_begin': datetime.datetime(2016, 3, 31, 0, 0, tzinfo=datetime.timezone.utc),\n",
+       "  'report_metadata_date_range_end': datetime.datetime(2016, 3, 31, 23, 59, 59, tzinfo=datetime.timezone.utc),\n",
+       "  'report_metadata_email': 'postmaster@dmarc.yahoo.com',\n",
+       "  'report_metadata_org_name': 'Yahoo! Inc.',\n",
+       "  'report_metadata_report_id': '1459475662.735732'})"
+      ]
+     },
+     "execution_count": 53,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "build_insert_command('reports', dmarc_reports[-1])"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 50,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "datetime.datetime(2015, 11, 22, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))"
+      ]
+     },
+     "execution_count": 50,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "datetime.datetime.fromtimestamp(int('1448150400'),  \n",
+    "                        tz=psycopg2.tz.FixedOffsetTimezone(offset=0, name='UTC'))"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 51,
    "metadata": {
     "collapsed": true
    },
@@ -487,6 +885,15 @@
    "source": [
     "conn.close()"
    ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {
+    "collapsed": true
+   },
+   "outputs": [],
+   "source": []
   }
  ],
  "metadata": {

diff --git a/dmarc_to_database b/dmarc_to_database
index a8141fdc108478097cc290e86890901c59cb6fd5..530d548b984fa8267eb57c46cfbacdf983441c1b 100755 (executable)
--- a/dmarc_to_database
+++ b/dmarc_to_database
@@ -87,10 +87,10 @@ field_maps = {'./policy_published/adkim': {'pg_field_name': 'policy_published_ad
   'pg_type': 'inet'},
  './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',
   'pg_table': 'reports',
-  'pg_type': 'timestamp'},
+  'pg_type': 'timestamptz'},
  './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',
   'pg_table': 'reports',
-  'pg_type': 'timestamp'},
+  'pg_type': 'timestamptz'},
  './report_metadata/email': {'pg_field_name': 'report_metadata_email',
   'pg_table': 'reports',
   'pg_type': 'varchar'},

diff --git a/make_database.ipynb b/make_database.ipynb
index 793df6c95f776738c6d0265212d6898754020379..a44d6d9fbb93e8887dba617a6cfa227d1754a113 100644 (file)
--- a/make_database.ipynb
+++ b/make_database.ipynb
@@ -2,7 +2,7 @@
  "cells": [
   {
    "cell_type": "code",
-   "execution_count": 14,
+   "execution_count": 9,
    "metadata": {
     "collapsed": true
    },
@@ -14,7 +14,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 15,
+   "execution_count": 10,
    "metadata": {
     "collapsed": true
    },
@@ -67,10 +67,10 @@
     "  'pg_type': 'inet'},\n",
     " './report_metadata/date_range/begin': {'pg_field_name': 'report_metadata_date_range_begin',\n",
     "  'pg_table': 'reports',\n",
-    "  'pg_type': 'timestamp'},\n",
+    "  'pg_type': 'timestamptz'},\n",
     " './report_metadata/date_range/end': {'pg_field_name': 'report_metadata_date_range_end',\n",
     "  'pg_table': 'reports',\n",
-    "  'pg_type': 'timestamp'},\n",
+    "  'pg_type': 'timestamptz'},\n",
     " './report_metadata/email': {'pg_field_name': 'report_metadata_email',\n",
     "  'pg_table': 'reports',\n",
     "  'pg_type': 'varchar'},\n",
@@ -84,7 +84,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 16,
+   "execution_count": 11,
    "metadata": {
     "collapsed": false
    },
@@ -95,7 +95,7 @@
        "['dmarc.ini']"
       ]
      },
-     "execution_count": 16,
+     "execution_count": 11,
      "metadata": {},
      "output_type": "execute_result"
     }
@@ -107,7 +107,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 17,
+   "execution_count": 12,
    "metadata": {
     "collapsed": false
    },
@@ -121,7 +121,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 18,
+   "execution_count": 13,
    "metadata": {
     "collapsed": false
    },
@@ -139,7 +139,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 19,
+   "execution_count": 14,
    "metadata": {
     "collapsed": false
    },
@@ -147,10 +147,10 @@
     {
      "data": {
       "text/plain": [
-       "'create table reports (id serial primary key, report_metadata_org_name varchar, policy_published_domain varchar, report_metadata_email varchar, report_metadata_date_range_begin timestamp, report_metadata_date_range_end timestamp, policy_published_adkim varchar, report_metadata_report_id varchar, policy_published_aspf varchar, policy_published_pct int, policy_published_p varchar);'"
+       "'create table reports (id serial primary key, policy_published_domain varchar, report_metadata_date_range_begin timestamptz, report_metadata_report_id varchar, report_metadata_date_range_end timestamptz, report_metadata_org_name varchar, policy_published_pct int, policy_published_p varchar, policy_published_aspf varchar, policy_published_adkim varchar, report_metadata_email varchar);'"
       ]
      },
-     "execution_count": 19,
+     "execution_count": 14,
      "metadata": {},
      "output_type": "execute_result"
     }
@@ -165,7 +165,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 20,
+   "execution_count": 15,
    "metadata": {
     "collapsed": false
    },
@@ -173,10 +173,10 @@
     {
      "data": {
       "text/plain": [
-       "'create table report_items (id serial primary key, report_id integer references reports, auth_results_dkim_result varchar, auth_results_spf_result varchar, count int, auth_results_dkim_domain varchar, source_ip inet, policy_evaluated_disposition varchar, identifiers_header_from varchar, auth_results_spf_domain varchar, policy_evaluated_dkim varchar, policy_evaluated_spf varchar);'"
+       "'create table report_items (id serial primary key, report_id integer references reports, auth_results_dkim_domain varchar, policy_evaluated_dkim varchar, source_ip inet, count int, auth_results_spf_domain varchar, policy_evaluated_spf varchar, policy_evaluated_disposition varchar, identifiers_header_from varchar, auth_results_dkim_result varchar, auth_results_spf_result varchar);'"
       ]
      },
-     "execution_count": 20,
+     "execution_count": 15,
      "metadata": {},
      "output_type": "execute_result"
     }
@@ -192,7 +192,7 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 21,
+   "execution_count": 16,
    "metadata": {
     "collapsed": true
    },

diff --git a/queries.ipynb b/queries.ipynb
index 38b22c920a3376b160c59743880c13690b930895..8eebb0788ea4b12650cd41648ad57e90a82773d4 100644 (file)
--- a/queries.ipynb
+++ b/queries.ipynb
@@ -43,6 +43,20 @@
     "config.read('dmarc.ini')"
    ]
   },
+  {
+   "cell_type": "code",
+   "execution_count": 8,
+   "metadata": {
+    "collapsed": true
+   },
+   "outputs": [],
+   "source": [
+    "conn = psycopg2.connect(host=config['database']['server'],\n",
+    "                        database=config['database']['database'], \n",
+    "                        user=config['database']['username'], \n",
+    "                        password=config['database']['password'])"
+   ]
+  },
   {
    "cell_type": "code",
    "execution_count": 3,
@@ -107,10 +121,6 @@
     }
    ],
    "source": [
-    "conn = psycopg2.connect(host=config['database']['server'],\n",
-    "                        database=config['database']['database'], \n",
-    "                        user=config['database']['username'], \n",
-    "                        password=config['database']['password'])\n",
     "with conn:\n",
     "    with conn.cursor() as cur:\n",
     "        cur.execute(\"\"\"\n",
@@ -315,6 +325,8 @@
        " ('1459216304.582931',),\n",
        " ('1459302353.261157',),\n",
        " ('14593873841710243963',),\n",
+       " ('1459391203.751219',),\n",
+       " ('1459475662.735732',),\n",
        " ('14661842628106423589',),\n",
        " ('14662396456930987863',),\n",
        " ('15111277194568576101',),\n",
@@ -387,6 +399,7 @@
        " ('8c177254c3cb41869dc3afab59f74c76@hotmail.com',),\n",
        " ('9037150256193522538',),\n",
        " ('9138782308848375092',),\n",
+       " ('9452413620101638402',),\n",
        " ('9594298247075547297',),\n",
        " ('a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com',),\n",
        " ('b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com',),\n",
@@ -422,6 +435,650 @@
     "sorted(results)"
    ]
   },
+  {
+   "cell_type": "code",
+   "execution_count": 10,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "[('1448246712.259522',\n",
+       "  datetime.datetime(2015, 11, 22, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('75eef2128eb84e9ca8e4837f3d4e31bd@hotmail.com',\n",
+       "  datetime.datetime(2015, 11, 22, 23, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('9138782308848375092',\n",
+       "  datetime.datetime(2015, 11, 23, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('840fc02d5cb847ec9f007fb236c0c190@hotmail.com',\n",
+       "  datetime.datetime(2015, 11, 23, 23, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('15111277194568576101',\n",
+       "  datetime.datetime(2015, 11, 24, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1448419746.206772',\n",
+       "  datetime.datetime(2015, 11, 24, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1448592471.754446',\n",
+       "  datetime.datetime(2015, 11, 26, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('c8e4fe772ae3434594e8396a8abf77b1@hotmail.com',\n",
+       "  datetime.datetime(2015, 11, 26, 23, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('2273327349069127175',\n",
+       "  datetime.datetime(2015, 11, 27, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1448851661.602960',\n",
+       "  datetime.datetime(2015, 11, 29, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1448938177.97045',\n",
+       "  datetime.datetime(2015, 11, 30, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('18203685646345145151',\n",
+       "  datetime.datetime(2015, 11, 30, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('12700770160536483846',\n",
+       "  datetime.datetime(2015, 12, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1449024342.601561',\n",
+       "  datetime.datetime(2015, 12, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1fadaaf3d9d544568a41042a1f42df05@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 1, 23, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('3544432548848738700',\n",
+       "  datetime.datetime(2015, 12, 2, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1449110875.443288',\n",
+       "  datetime.datetime(2015, 12, 2, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1449197294.177690',\n",
+       "  datetime.datetime(2015, 12, 3, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('10356602625290246518',\n",
+       "  datetime.datetime(2015, 12, 4, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1449283514.868026',\n",
+       "  datetime.datetime(2015, 12, 4, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('4654132158287507198',\n",
+       "  datetime.datetime(2015, 12, 5, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('5164714352250096219',\n",
+       "  datetime.datetime(2015, 12, 7, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1449542804.259788',\n",
+       "  datetime.datetime(2015, 12, 7, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('20246ec4a70041caa276560032fdc595@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 7, 23, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('14089985608998676943',\n",
+       "  datetime.datetime(2015, 12, 8, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1449629119.120001',\n",
+       "  datetime.datetime(2015, 12, 8, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('c8f339f8f3364d78820dd03a84d11daa@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 8, 23, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1233953228184721030',\n",
+       "  datetime.datetime(2015, 12, 9, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('17984785070730076605',\n",
+       "  datetime.datetime(2015, 12, 11, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1449888489.668429',\n",
+       "  datetime.datetime(2015, 12, 11, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('121bb39637c14847a40669dd3e7cdf49@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 11, 23, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('14661842628106423589',\n",
+       "  datetime.datetime(2015, 12, 12, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1449974899.615312',\n",
+       "  datetime.datetime(2015, 12, 12, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('7834597727856283739',\n",
+       "  datetime.datetime(2015, 12, 14, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('f94f00dee713479fb8bfdbcad539da88@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 14, 23, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('17659080448126516391',\n",
+       "  datetime.datetime(2015, 12, 15, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1450234735.690828',\n",
+       "  datetime.datetime(2015, 12, 15, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('17880573400612806781',\n",
+       "  datetime.datetime(2015, 12, 16, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1451184218.12383',\n",
+       "  datetime.datetime(2015, 12, 26, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('6505353761044749597',\n",
+       "  datetime.datetime(2015, 12, 29, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('02aca4c6bb144bb6b54773374c14a43a@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 29, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1451443783.692950',\n",
+       "  datetime.datetime(2015, 12, 29, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('12137203242660163862',\n",
+       "  datetime.datetime(2015, 12, 30, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('d1019291012e4f4589f59f1dacef31a3@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 30, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1451616408.476341',\n",
+       "  datetime.datetime(2015, 12, 31, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('11743474449139138027',\n",
+       "  datetime.datetime(2016, 1, 5, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('e245ef81f05b4697bea34bed977d8286@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 5, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1452134765.308922',\n",
+       "  datetime.datetime(2016, 1, 6, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('271c80b9234a4c99bb9608abc5470c99@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 8, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('15974729567081493290',\n",
+       "  datetime.datetime(2016, 1, 9, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('ba1e9c35eec5428b8c3d6a32d25daa46@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 9, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1452393953.83099',\n",
+       "  datetime.datetime(2016, 1, 9, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('d71792d8afc246e9a4756bd13e600b59@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 10, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1452480188.666644',\n",
+       "  datetime.datetime(2016, 1, 10, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1452566814.805501',\n",
+       "  datetime.datetime(2016, 1, 11, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('fc0750780e0d4b1395c4c9f41cb9791f@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 12, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1452653275.294149',\n",
+       "  datetime.datetime(2016, 1, 12, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1452741832.342075',\n",
+       "  datetime.datetime(2016, 1, 13, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('2884439811958006165',\n",
+       "  datetime.datetime(2016, 1, 13, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('3508955360705645678',\n",
+       "  datetime.datetime(2016, 1, 12, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1452826445.971145',\n",
+       "  datetime.datetime(2016, 1, 14, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1452912512.114840',\n",
+       "  datetime.datetime(2016, 1, 15, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('eda16c25bbdb48b2bae0d18eab13f736@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 16, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('724976339745122723',\n",
+       "  datetime.datetime(2016, 1, 17, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1453084984.369330',\n",
+       "  datetime.datetime(2016, 1, 17, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1453171440.157305',\n",
+       "  datetime.datetime(2016, 1, 18, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1862812391966551302',\n",
+       "  datetime.datetime(2016, 1, 19, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1453258067.440890',\n",
+       "  datetime.datetime(2016, 1, 19, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1453344550.144698',\n",
+       "  datetime.datetime(2016, 1, 20, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('2002148329698541727',\n",
+       "  datetime.datetime(2016, 1, 21, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('6110291457768008366',\n",
+       "  datetime.datetime(2016, 1, 22, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('de651cefbc0749a3aa3d91cdc5084f5a@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 22, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1453517236.399019',\n",
+       "  datetime.datetime(2016, 1, 22, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('14593873841710243963',\n",
+       "  datetime.datetime(2016, 1, 24, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('ba64911adb0743d0b926588647ec5dcf@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 24, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1453689981.768611',\n",
+       "  datetime.datetime(2016, 1, 24, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('13959504937502303973',\n",
+       "  datetime.datetime(2016, 1, 25, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('3eba8b9e31724809aaf8d517e5656c4b@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 25, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1453776417.898739',\n",
+       "  datetime.datetime(2016, 1, 25, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('9594298247075547297',\n",
+       "  datetime.datetime(2016, 1, 26, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('d77c5b90344149f5bb7b50ff69400aeb@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 26, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1453862958.482897',\n",
+       "  datetime.datetime(2016, 1, 26, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('3267388508667988463',\n",
+       "  datetime.datetime(2016, 1, 27, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('580ca0a74948493ba8b7c6e62b8c81d9@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 28, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('6876229717666715946',\n",
+       "  datetime.datetime(2016, 1, 28, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('6474240574237085621',\n",
+       "  datetime.datetime(2016, 1, 31, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1454381343.228960',\n",
+       "  datetime.datetime(2016, 2, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('77b6c2aa32bf440aa240195db229cd4a@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 1, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1922265692629464594',\n",
+       "  datetime.datetime(2016, 2, 2, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1454467952.258012',\n",
+       "  datetime.datetime(2016, 2, 2, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1454640482.903992',\n",
+       "  datetime.datetime(2016, 2, 4, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('16409503372348966326',\n",
+       "  datetime.datetime(2016, 2, 4, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('456ffff78456494fbaed47b465259d46@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 4, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('543258397004515473',\n",
+       "  datetime.datetime(2016, 2, 5, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('4158049796460953654',\n",
+       "  datetime.datetime(2016, 2, 7, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 7, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1454986282.636073',\n",
+       "  datetime.datetime(2016, 2, 8, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('8986675713054039362',\n",
+       "  datetime.datetime(2016, 2, 9, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('30d5ef78d6d746a4a777c56e7ca6f1c6@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 9, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1455072659.49250',\n",
+       "  datetime.datetime(2016, 2, 9, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('10744929815548178249',\n",
+       "  datetime.datetime(2016, 2, 10, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1178c4fe34364ff39cb6e24a51219093@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 10, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('730219275619457',\n",
+       "  datetime.datetime(2016, 2, 11, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('164180405175632647',\n",
+       "  datetime.datetime(2016, 2, 12, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('16520810497197721925',\n",
+       "  datetime.datetime(2016, 2, 13, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('13f28e952d67451281b90d55eee9c7c3@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 13, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1455418081.446180',\n",
+       "  datetime.datetime(2016, 2, 13, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('641b4cc500334a7eb3f649b21af003ca@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 15, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('14416654486328871570',\n",
+       "  datetime.datetime(2016, 2, 16, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('dbe2f49dbea947428b179e0ecec5f813@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 16, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1455679107.469767',\n",
+       "  datetime.datetime(2016, 2, 16, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('7815164892280952980',\n",
+       "  datetime.datetime(2016, 2, 17, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('511d6fbb8484454b916ad8d799938390@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 17, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1455763965.140751',\n",
+       "  datetime.datetime(2016, 2, 17, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('10491829640821461358',\n",
+       "  datetime.datetime(2016, 2, 18, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('13702792868066092542',\n",
+       "  datetime.datetime(2016, 2, 19, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1456109267.60200',\n",
+       "  datetime.datetime(2016, 2, 21, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('2cfe3d0ec75b43f89a6a7ae57777e25c@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 22, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('9037150256193522538',\n",
+       "  datetime.datetime(2016, 2, 23, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('e327753089e04950906848d104a93695@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 23, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1456282325.455667',\n",
+       "  datetime.datetime(2016, 2, 23, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('3584764172722215181',\n",
+       "  datetime.datetime(2016, 2, 24, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1456455410.661669',\n",
+       "  datetime.datetime(2016, 2, 25, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('16824795799639390032',\n",
+       "  datetime.datetime(2016, 2, 25, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('14217880258295310962',\n",
+       "  datetime.datetime(2016, 2, 26, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1456541727.873510',\n",
+       "  datetime.datetime(2016, 2, 26, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1102163566627474204',\n",
+       "  datetime.datetime(2016, 2, 28, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1456713880.905762',\n",
+       "  datetime.datetime(2016, 2, 28, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1456800606.884839',\n",
+       "  datetime.datetime(2016, 2, 29, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1bcc49be66744cc7a70ae1cc53515bbf@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 29, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('11019573554877343650',\n",
+       "  datetime.datetime(2016, 3, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1456886761.643377',\n",
+       "  datetime.datetime(2016, 3, 1, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('36dd4e2b3f424bb6919d2d5d4c77c8dd@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 2, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 3, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1457059537.625770',\n",
+       "  datetime.datetime(2016, 3, 3, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('85136d21c514431e9f5cf9d36faa4a22@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 4, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1457146943.63077',\n",
+       "  datetime.datetime(2016, 3, 4, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1457232742.472142',\n",
+       "  datetime.datetime(2016, 3, 5, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('ea3a96df66a845c5a666068ef8ca1368@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 5, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('17044038014166863505',\n",
+       "  datetime.datetime(2016, 3, 6, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1457318880.887129',\n",
+       "  datetime.datetime(2016, 3, 6, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('3689737760348689620',\n",
+       "  datetime.datetime(2016, 3, 7, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('726a3261dfab4b4590b5fc898c561b08@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 7, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('129a9dc3ff2e40ae8a3890ecc1c291d9@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 8, 15, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1457491692.793016',\n",
+       "  datetime.datetime(2016, 3, 8, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1207168150882246442',\n",
+       "  datetime.datetime(2016, 3, 8, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('10706879170593875813',\n",
+       "  datetime.datetime(2016, 3, 9, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('4226319448276355598',\n",
+       "  datetime.datetime(2016, 3, 11, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1457750956.253773',\n",
+       "  datetime.datetime(2016, 3, 11, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('12434676377746978866',\n",
+       "  datetime.datetime(2016, 3, 13, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('43e7b5e2e0fb400aa914058360c8aaab@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 13, 14, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1457919850.989656',\n",
+       "  datetime.datetime(2016, 3, 13, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('594749737641304465',\n",
+       "  datetime.datetime(2016, 3, 14, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('18c9a966da81401e80ca2a22310ffee6@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 14, 14, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1458006833.731671',\n",
+       "  datetime.datetime(2016, 3, 14, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('6996084840178215735',\n",
+       "  datetime.datetime(2016, 3, 15, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1458093082.193350',\n",
+       "  datetime.datetime(2016, 3, 15, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('3188090359734380692',\n",
+       "  datetime.datetime(2016, 3, 17, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('7006513620611789399',\n",
+       "  datetime.datetime(2016, 3, 18, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1458353823.362308',\n",
+       "  datetime.datetime(2016, 3, 18, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('17071564483265101388',\n",
+       "  datetime.datetime(2016, 3, 19, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1458524834.32161',\n",
+       "  datetime.datetime(2016, 3, 20, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1458611429.595569',\n",
+       "  datetime.datetime(2016, 3, 21, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('14662396456930987863',\n",
+       "  datetime.datetime(2016, 3, 23, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1458784545.303847',\n",
+       "  datetime.datetime(2016, 3, 23, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('13032699446207263761',\n",
+       "  datetime.datetime(2016, 3, 24, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('8426152571808739514',\n",
+       "  datetime.datetime(2016, 3, 25, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1458957186.548175',\n",
+       "  datetime.datetime(2016, 3, 25, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('2150510829392606201',\n",
+       "  datetime.datetime(2016, 3, 26, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('68aad5080a774e2c997d159b546569b9@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 26, 17, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('1459129809.695034',\n",
+       "  datetime.datetime(2016, 3, 27, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('16143280651570354241',\n",
+       "  datetime.datetime(2016, 3, 27, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),\n",
+       " ('8c177254c3cb41869dc3afab59f74c76@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 27, 18, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('15410706527896810898',\n",
+       "  datetime.datetime(2016, 3, 28, 1, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('1459216304.582931',\n",
+       "  datetime.datetime(2016, 3, 28, 1, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('15497495941279624940',\n",
+       "  datetime.datetime(2016, 3, 29, 1, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('1459302353.261157',\n",
+       "  datetime.datetime(2016, 3, 29, 1, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 29, 18, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('15185964531645951164',\n",
+       "  datetime.datetime(2016, 3, 30, 1, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('1459391203.751219',\n",
+       "  datetime.datetime(2016, 3, 30, 1, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('9452413620101638402',\n",
+       "  datetime.datetime(2016, 3, 31, 1, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))),\n",
+       " ('1459475662.735732',\n",
+       "  datetime.datetime(2016, 3, 31, 1, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None)))]"
+      ]
+     },
+     "execution_count": 10,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "with conn:\n",
+    "    with conn.cursor() as cur:\n",
+    "        cur.execute(\"\"\"\n",
+    "        select report_metadata_report_id, report_metadata_date_range_begin\n",
+    "        from reports\"\"\")\n",
+    "        results = cur.fetchall()\n",
+    "results"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": 11,
+   "metadata": {
+    "collapsed": false
+   },
+   "outputs": [
+    {
+     "data": {
+      "text/plain": [
+       "[('1448246712.259522', datetime.datetime(2015, 11, 22, 0, 0)),\n",
+       " ('75eef2128eb84e9ca8e4837f3d4e31bd@hotmail.com',\n",
+       "  datetime.datetime(2015, 11, 22, 23, 0)),\n",
+       " ('9138782308848375092', datetime.datetime(2015, 11, 23, 0, 0)),\n",
+       " ('840fc02d5cb847ec9f007fb236c0c190@hotmail.com',\n",
+       "  datetime.datetime(2015, 11, 23, 23, 0)),\n",
+       " ('15111277194568576101', datetime.datetime(2015, 11, 24, 0, 0)),\n",
+       " ('1448419746.206772', datetime.datetime(2015, 11, 24, 0, 0)),\n",
+       " ('1448592471.754446', datetime.datetime(2015, 11, 26, 0, 0)),\n",
+       " ('c8e4fe772ae3434594e8396a8abf77b1@hotmail.com',\n",
+       "  datetime.datetime(2015, 11, 26, 23, 0)),\n",
+       " ('2273327349069127175', datetime.datetime(2015, 11, 27, 0, 0)),\n",
+       " ('1448851661.602960', datetime.datetime(2015, 11, 29, 0, 0)),\n",
+       " ('1448938177.97045', datetime.datetime(2015, 11, 30, 0, 0)),\n",
+       " ('18203685646345145151', datetime.datetime(2015, 11, 30, 0, 0)),\n",
+       " ('12700770160536483846', datetime.datetime(2015, 12, 1, 0, 0)),\n",
+       " ('1449024342.601561', datetime.datetime(2015, 12, 1, 0, 0)),\n",
+       " ('1fadaaf3d9d544568a41042a1f42df05@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 1, 23, 0)),\n",
+       " ('3544432548848738700', datetime.datetime(2015, 12, 2, 0, 0)),\n",
+       " ('1449110875.443288', datetime.datetime(2015, 12, 2, 0, 0)),\n",
+       " ('1449197294.177690', datetime.datetime(2015, 12, 3, 0, 0)),\n",
+       " ('10356602625290246518', datetime.datetime(2015, 12, 4, 0, 0)),\n",
+       " ('1449283514.868026', datetime.datetime(2015, 12, 4, 0, 0)),\n",
+       " ('4654132158287507198', datetime.datetime(2015, 12, 5, 0, 0)),\n",
+       " ('5164714352250096219', datetime.datetime(2015, 12, 7, 0, 0)),\n",
+       " ('1449542804.259788', datetime.datetime(2015, 12, 7, 0, 0)),\n",
+       " ('20246ec4a70041caa276560032fdc595@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 7, 23, 0)),\n",
+       " ('14089985608998676943', datetime.datetime(2015, 12, 8, 0, 0)),\n",
+       " ('1449629119.120001', datetime.datetime(2015, 12, 8, 0, 0)),\n",
+       " ('c8f339f8f3364d78820dd03a84d11daa@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 8, 23, 0)),\n",
+       " ('1233953228184721030', datetime.datetime(2015, 12, 9, 0, 0)),\n",
+       " ('17984785070730076605', datetime.datetime(2015, 12, 11, 0, 0)),\n",
+       " ('1449888489.668429', datetime.datetime(2015, 12, 11, 0, 0)),\n",
+       " ('121bb39637c14847a40669dd3e7cdf49@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 11, 23, 0)),\n",
+       " ('14661842628106423589', datetime.datetime(2015, 12, 12, 0, 0)),\n",
+       " ('1449974899.615312', datetime.datetime(2015, 12, 12, 0, 0)),\n",
+       " ('7834597727856283739', datetime.datetime(2015, 12, 14, 0, 0)),\n",
+       " ('f94f00dee713479fb8bfdbcad539da88@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 14, 23, 0)),\n",
+       " ('17659080448126516391', datetime.datetime(2015, 12, 15, 0, 0)),\n",
+       " ('1450234735.690828', datetime.datetime(2015, 12, 15, 0, 0)),\n",
+       " ('17880573400612806781', datetime.datetime(2015, 12, 16, 0, 0)),\n",
+       " ('1451184218.12383', datetime.datetime(2015, 12, 26, 0, 0)),\n",
+       " ('6505353761044749597', datetime.datetime(2015, 12, 29, 0, 0)),\n",
+       " ('02aca4c6bb144bb6b54773374c14a43a@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 29, 15, 0)),\n",
+       " ('1451443783.692950', datetime.datetime(2015, 12, 29, 0, 0)),\n",
+       " ('12137203242660163862', datetime.datetime(2015, 12, 30, 0, 0)),\n",
+       " ('d1019291012e4f4589f59f1dacef31a3@hotmail.com',\n",
+       "  datetime.datetime(2015, 12, 30, 15, 0)),\n",
+       " ('1451616408.476341', datetime.datetime(2015, 12, 31, 0, 0)),\n",
+       " ('11743474449139138027', datetime.datetime(2016, 1, 5, 0, 0)),\n",
+       " ('e245ef81f05b4697bea34bed977d8286@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 5, 15, 0)),\n",
+       " ('1452134765.308922', datetime.datetime(2016, 1, 6, 0, 0)),\n",
+       " ('271c80b9234a4c99bb9608abc5470c99@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 8, 15, 0)),\n",
+       " ('15974729567081493290', datetime.datetime(2016, 1, 9, 0, 0)),\n",
+       " ('ba1e9c35eec5428b8c3d6a32d25daa46@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 9, 15, 0)),\n",
+       " ('1452393953.83099', datetime.datetime(2016, 1, 9, 0, 0)),\n",
+       " ('d71792d8afc246e9a4756bd13e600b59@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 10, 15, 0)),\n",
+       " ('1452480188.666644', datetime.datetime(2016, 1, 10, 0, 0)),\n",
+       " ('1452566814.805501', datetime.datetime(2016, 1, 11, 0, 0)),\n",
+       " ('fc0750780e0d4b1395c4c9f41cb9791f@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 12, 15, 0)),\n",
+       " ('1452653275.294149', datetime.datetime(2016, 1, 12, 0, 0)),\n",
+       " ('1452741832.342075', datetime.datetime(2016, 1, 13, 0, 0)),\n",
+       " ('2884439811958006165', datetime.datetime(2016, 1, 13, 0, 0)),\n",
+       " ('3508955360705645678', datetime.datetime(2016, 1, 12, 0, 0)),\n",
+       " ('1452826445.971145', datetime.datetime(2016, 1, 14, 0, 0)),\n",
+       " ('1452912512.114840', datetime.datetime(2016, 1, 15, 0, 0)),\n",
+       " ('eda16c25bbdb48b2bae0d18eab13f736@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 16, 15, 0)),\n",
+       " ('724976339745122723', datetime.datetime(2016, 1, 17, 0, 0)),\n",
+       " ('1453084984.369330', datetime.datetime(2016, 1, 17, 0, 0)),\n",
+       " ('1453171440.157305', datetime.datetime(2016, 1, 18, 0, 0)),\n",
+       " ('1862812391966551302', datetime.datetime(2016, 1, 19, 0, 0)),\n",
+       " ('1453258067.440890', datetime.datetime(2016, 1, 19, 0, 0)),\n",
+       " ('1453344550.144698', datetime.datetime(2016, 1, 20, 0, 0)),\n",
+       " ('2002148329698541727', datetime.datetime(2016, 1, 21, 0, 0)),\n",
+       " ('6110291457768008366', datetime.datetime(2016, 1, 22, 0, 0)),\n",
+       " ('de651cefbc0749a3aa3d91cdc5084f5a@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 22, 15, 0)),\n",
+       " ('1453517236.399019', datetime.datetime(2016, 1, 22, 0, 0)),\n",
+       " ('14593873841710243963', datetime.datetime(2016, 1, 24, 0, 0)),\n",
+       " ('ba64911adb0743d0b926588647ec5dcf@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 24, 15, 0)),\n",
+       " ('1453689981.768611', datetime.datetime(2016, 1, 24, 0, 0)),\n",
+       " ('13959504937502303973', datetime.datetime(2016, 1, 25, 0, 0)),\n",
+       " ('3eba8b9e31724809aaf8d517e5656c4b@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 25, 15, 0)),\n",
+       " ('1453776417.898739', datetime.datetime(2016, 1, 25, 0, 0)),\n",
+       " ('9594298247075547297', datetime.datetime(2016, 1, 26, 0, 0)),\n",
+       " ('d77c5b90344149f5bb7b50ff69400aeb@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 26, 15, 0)),\n",
+       " ('1453862958.482897', datetime.datetime(2016, 1, 26, 0, 0)),\n",
+       " ('3267388508667988463', datetime.datetime(2016, 1, 27, 0, 0)),\n",
+       " ('580ca0a74948493ba8b7c6e62b8c81d9@hotmail.com',\n",
+       "  datetime.datetime(2016, 1, 28, 15, 0)),\n",
+       " ('6876229717666715946', datetime.datetime(2016, 1, 28, 0, 0)),\n",
+       " ('6474240574237085621', datetime.datetime(2016, 1, 31, 0, 0)),\n",
+       " ('1454381343.228960', datetime.datetime(2016, 2, 1, 0, 0)),\n",
+       " ('77b6c2aa32bf440aa240195db229cd4a@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 1, 15, 0)),\n",
+       " ('1922265692629464594', datetime.datetime(2016, 2, 2, 0, 0)),\n",
+       " ('1454467952.258012', datetime.datetime(2016, 2, 2, 0, 0)),\n",
+       " ('1454640482.903992', datetime.datetime(2016, 2, 4, 0, 0)),\n",
+       " ('16409503372348966326', datetime.datetime(2016, 2, 4, 0, 0)),\n",
+       " ('456ffff78456494fbaed47b465259d46@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 4, 15, 0)),\n",
+       " ('543258397004515473', datetime.datetime(2016, 2, 5, 0, 0)),\n",
+       " ('4158049796460953654', datetime.datetime(2016, 2, 7, 0, 0)),\n",
+       " ('a6b6aec1af1647eb8c6fd83baaed6bbe@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 7, 15, 0)),\n",
+       " ('1454986282.636073', datetime.datetime(2016, 2, 8, 0, 0)),\n",
+       " ('8986675713054039362', datetime.datetime(2016, 2, 9, 0, 0)),\n",
+       " ('30d5ef78d6d746a4a777c56e7ca6f1c6@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 9, 15, 0)),\n",
+       " ('1455072659.49250', datetime.datetime(2016, 2, 9, 0, 0)),\n",
+       " ('10744929815548178249', datetime.datetime(2016, 2, 10, 0, 0)),\n",
+       " ('1178c4fe34364ff39cb6e24a51219093@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 10, 15, 0)),\n",
+       " ('730219275619457', datetime.datetime(2016, 2, 11, 0, 0)),\n",
+       " ('164180405175632647', datetime.datetime(2016, 2, 12, 0, 0)),\n",
+       " ('16520810497197721925', datetime.datetime(2016, 2, 13, 0, 0)),\n",
+       " ('13f28e952d67451281b90d55eee9c7c3@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 13, 15, 0)),\n",
+       " ('1455418081.446180', datetime.datetime(2016, 2, 13, 0, 0)),\n",
+       " ('641b4cc500334a7eb3f649b21af003ca@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 15, 15, 0)),\n",
+       " ('14416654486328871570', datetime.datetime(2016, 2, 16, 0, 0)),\n",
+       " ('dbe2f49dbea947428b179e0ecec5f813@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 16, 15, 0)),\n",
+       " ('1455679107.469767', datetime.datetime(2016, 2, 16, 0, 0)),\n",
+       " ('7815164892280952980', datetime.datetime(2016, 2, 17, 0, 0)),\n",
+       " ('511d6fbb8484454b916ad8d799938390@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 17, 15, 0)),\n",
+       " ('1455763965.140751', datetime.datetime(2016, 2, 17, 0, 0)),\n",
+       " ('10491829640821461358', datetime.datetime(2016, 2, 18, 0, 0)),\n",
+       " ('13702792868066092542', datetime.datetime(2016, 2, 19, 0, 0)),\n",
+       " ('1456109267.60200', datetime.datetime(2016, 2, 21, 0, 0)),\n",
+       " ('2cfe3d0ec75b43f89a6a7ae57777e25c@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 22, 15, 0)),\n",
+       " ('9037150256193522538', datetime.datetime(2016, 2, 23, 0, 0)),\n",
+       " ('e327753089e04950906848d104a93695@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 23, 15, 0)),\n",
+       " ('1456282325.455667', datetime.datetime(2016, 2, 23, 0, 0)),\n",
+       " ('3584764172722215181', datetime.datetime(2016, 2, 24, 0, 0)),\n",
+       " ('1456455410.661669', datetime.datetime(2016, 2, 25, 0, 0)),\n",
+       " ('16824795799639390032', datetime.datetime(2016, 2, 25, 0, 0)),\n",
+       " ('14217880258295310962', datetime.datetime(2016, 2, 26, 0, 0)),\n",
+       " ('1456541727.873510', datetime.datetime(2016, 2, 26, 0, 0)),\n",
+       " ('1102163566627474204', datetime.datetime(2016, 2, 28, 0, 0)),\n",
+       " ('1456713880.905762', datetime.datetime(2016, 2, 28, 0, 0)),\n",
+       " ('1456800606.884839', datetime.datetime(2016, 2, 29, 0, 0)),\n",
+       " ('1bcc49be66744cc7a70ae1cc53515bbf@hotmail.com',\n",
+       "  datetime.datetime(2016, 2, 29, 15, 0)),\n",
+       " ('11019573554877343650', datetime.datetime(2016, 3, 1, 0, 0)),\n",
+       " ('1456886761.643377', datetime.datetime(2016, 3, 1, 0, 0)),\n",
+       " ('36dd4e2b3f424bb6919d2d5d4c77c8dd@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 2, 15, 0)),\n",
+       " ('b6d15133598b4e00b21c8e2b7cbec05e@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 3, 15, 0)),\n",
+       " ('1457059537.625770', datetime.datetime(2016, 3, 3, 0, 0)),\n",
+       " ('85136d21c514431e9f5cf9d36faa4a22@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 4, 15, 0)),\n",
+       " ('1457146943.63077', datetime.datetime(2016, 3, 4, 0, 0)),\n",
+       " ('1457232742.472142', datetime.datetime(2016, 3, 5, 0, 0)),\n",
+       " ('ea3a96df66a845c5a666068ef8ca1368@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 5, 15, 0)),\n",
+       " ('17044038014166863505', datetime.datetime(2016, 3, 6, 0, 0)),\n",
+       " ('1457318880.887129', datetime.datetime(2016, 3, 6, 0, 0)),\n",
+       " ('3689737760348689620', datetime.datetime(2016, 3, 7, 0, 0)),\n",
+       " ('726a3261dfab4b4590b5fc898c561b08@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 7, 15, 0)),\n",
+       " ('129a9dc3ff2e40ae8a3890ecc1c291d9@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 8, 15, 0)),\n",
+       " ('1457491692.793016', datetime.datetime(2016, 3, 8, 0, 0)),\n",
+       " ('1207168150882246442', datetime.datetime(2016, 3, 8, 0, 0)),\n",
+       " ('10706879170593875813', datetime.datetime(2016, 3, 9, 0, 0)),\n",
+       " ('4226319448276355598', datetime.datetime(2016, 3, 11, 0, 0)),\n",
+       " ('1457750956.253773', datetime.datetime(2016, 3, 11, 0, 0)),\n",
+       " ('12434676377746978866', datetime.datetime(2016, 3, 13, 0, 0)),\n",
+       " ('43e7b5e2e0fb400aa914058360c8aaab@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 13, 14, 0)),\n",
+       " ('1457919850.989656', datetime.datetime(2016, 3, 13, 0, 0)),\n",
+       " ('594749737641304465', datetime.datetime(2016, 3, 14, 0, 0)),\n",
+       " ('18c9a966da81401e80ca2a22310ffee6@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 14, 14, 0)),\n",
+       " ('1458006833.731671', datetime.datetime(2016, 3, 14, 0, 0)),\n",
+       " ('6996084840178215735', datetime.datetime(2016, 3, 15, 0, 0)),\n",
+       " ('1458093082.193350', datetime.datetime(2016, 3, 15, 0, 0)),\n",
+       " ('3188090359734380692', datetime.datetime(2016, 3, 17, 0, 0)),\n",
+       " ('7006513620611789399', datetime.datetime(2016, 3, 18, 0, 0)),\n",
+       " ('1458353823.362308', datetime.datetime(2016, 3, 18, 0, 0)),\n",
+       " ('17071564483265101388', datetime.datetime(2016, 3, 19, 0, 0)),\n",
+       " ('1458524834.32161', datetime.datetime(2016, 3, 20, 0, 0)),\n",
+       " ('1458611429.595569', datetime.datetime(2016, 3, 21, 0, 0)),\n",
+       " ('14662396456930987863', datetime.datetime(2016, 3, 23, 0, 0)),\n",
+       " ('1458784545.303847', datetime.datetime(2016, 3, 23, 0, 0)),\n",
+       " ('13032699446207263761', datetime.datetime(2016, 3, 24, 0, 0)),\n",
+       " ('8426152571808739514', datetime.datetime(2016, 3, 25, 0, 0)),\n",
+       " ('1458957186.548175', datetime.datetime(2016, 3, 25, 0, 0)),\n",
+       " ('2150510829392606201', datetime.datetime(2016, 3, 26, 0, 0)),\n",
+       " ('68aad5080a774e2c997d159b546569b9@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 26, 17, 0)),\n",
+       " ('1459129809.695034', datetime.datetime(2016, 3, 27, 0, 0)),\n",
+       " ('16143280651570354241', datetime.datetime(2016, 3, 27, 0, 0)),\n",
+       " ('8c177254c3cb41869dc3afab59f74c76@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 27, 17, 0)),\n",
+       " ('15410706527896810898', datetime.datetime(2016, 3, 28, 0, 0)),\n",
+       " ('1459216304.582931', datetime.datetime(2016, 3, 28, 0, 0)),\n",
+       " ('15497495941279624940', datetime.datetime(2016, 3, 29, 0, 0)),\n",
+       " ('1459302353.261157', datetime.datetime(2016, 3, 29, 0, 0)),\n",
+       " ('7773a696f4a54f1e8c01f4644fbb94ee@hotmail.com',\n",
+       "  datetime.datetime(2016, 3, 29, 17, 0)),\n",
+       " ('15185964531645951164', datetime.datetime(2016, 3, 30, 0, 0)),\n",
+       " ('1459391203.751219', datetime.datetime(2016, 3, 30, 0, 0)),\n",
+       " ('9452413620101638402', datetime.datetime(2016, 3, 31, 0, 0)),\n",
+       " ('1459475662.735732', datetime.datetime(2016, 3, 31, 0, 0))]"
+      ]
+     },
+     "execution_count": 11,
+     "metadata": {},
+     "output_type": "execute_result"
+    }
+   ],
+   "source": [
+    "with conn:\n",
+    "    with conn.cursor() as cur:\n",
+    "        cur.execute(\"\"\"\n",
+    "        select report_metadata_report_id, report_metadata_date_range_begin at time zone 'UTC'\n",
+    "        from reports\"\"\")\n",
+    "        results = cur.fetchall()\n",
+    "results"
+   ]
+  },
   {
    "cell_type": "code",
    "execution_count": 7,
@@ -430,7 +1087,7 @@
    },
    "outputs": [],
    "source": [
-    "conn.close()"
+    "# conn.close()"
    ]
   }
  ],

diff --git a/write-to-database.ipynb b/write-to-database.ipynb
index 61cab594b3e23feffecea8387ac5754fe0140535..331d569fa7000155053a444936b8df04a42c8406 100644 (file)
--- a/write-to-database.ipynb
+++ b/write-to-database.ipynb
@@ -733,8 +733,8 @@
     "              'pg_type': 'varchar'}\n",
     "             for name in xpath_of(root2)\n",
     "             if 'record' in name})\n",
-    "field_maps['./report_metadata/date_range/begin']['pg_type'] = 'timestamp'\n",
-    "field_maps['./report_metadata/date_range/end']['pg_type'] = 'timestamp'\n",
+    "field_maps['./report_metadata/date_range/begin']['pg_type'] = 'timestamptz'\n",
+    "field_maps['./report_metadata/date_range/end']['pg_type'] = 'timestamptz'\n",
     "field_maps['./policy_published/pct']['pg_type'] = 'int'\n",
     "field_maps['./record[{}]/row/count']['pg_type'] = 'int'\n",
     "field_maps['./record[{}]/row/source_ip']['pg_type'] = 'inet'\n",
@@ -1291,8 +1291,9 @@
     "    field_names += [field_maps[f]['pg_field_name']]\n",
     "    if field_maps[f]['pg_type'] == 'int':\n",
     "        values[field_maps[f]['pg_field_name']] = int(rep.find(f).text)\n",
-    "    elif field_maps[f]['pg_type'] == 'timestamp':\n",
-    "        values[field_maps[f]['pg_field_name']] = datetime.datetime.utcfromtimestamp(int(rep.find(f).text))\n",
+    "    elif field_maps[f]['pg_type'] == 'timestamptz':\n",
+    "        values[field_maps[f]['pg_field_name']] = datetime.datetime.fromtimestamp(int(rep.find(f).text), \n",
+    "                                                                                 tz=datetime.timezone.utc)\n",
     "    elif field_maps[f]['pg_type'] == 'inet':\n",
     "        values[field_maps[f]['pg_field_name']] = maybe_strip(rep.find(f).text)\n",
     "    else:\n",