From 9705b435f1eb6db4a872858989d8af455c1dc890 Mon Sep 17 00:00:00 2001 From: Neil Smith Date: Fri, 1 Apr 2016 22:56:06 +0100 Subject: [PATCH] Now using timestamps with timezones --- complete.ipynb | 629 ++++++++++++++++++++++++++++++------- dmarc_to_database | 4 +- make_database.ipynb | 30 +- queries.ipynb | 667 +++++++++++++++++++++++++++++++++++++++- write-to-database.ipynb | 9 +- 5 files changed, 1202 insertions(+), 137 deletions(-) diff --git a/complete.ipynb b/complete.ipynb index 735b78f..ccbdaf0 100644 --- 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 a8141fd..530d548 100755 --- 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 793df6c..a44d6d9 100644 --- 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 38b22c9..8eebb07 100644 --- 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 61cab59..331d569 100644 --- 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", -- 2.34.1