import awswrangler as wr
df = wr.athena.read_sql_query(
sql="SELECT * FROM illumination where device_id = '00158d0006c9d5ed' order by timestamp desc limit 10 /*3회의실조도센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0006c9d5ed | 1638142531000 | 2021-11-29 08:35:31 | a2_sen_cds_01 | c5a28dfd | reported | 6 | 1 | 6 | 2021 | 11 | 29 | 8 |
1 | 00158d0006c9d5ed | 1638138628000 | 2021-11-29 07:30:28 | a2_sen_cds_01 | 339ffde9 | reported | 6 | 1 | 1 | 2021 | 11 | 29 | 7 |
2 | 00158d0006c9d5ed | 1638134714000 | 2021-11-29 06:25:14 | a2_sen_cds_01 | 2dfd69c4 | reported | 6 | 1 | 1 | 2021 | 11 | 29 | 6 |
3 | 00158d0006c9d5ed | 1638130206000 | 2021-11-29 05:10:06 | a2_sen_cds_01 | 55fcca49 | reported | 6 | 1 | 1 | 2021 | 11 | 29 | 5 |
4 | 00158d0006c9d5ed | 1637987718000 | 2021-11-27 13:35:18 | a2_sen_cds_01 | 07cd0ed4 | reported | 6 | 1 | 1 | 2021 | 11 | 27 | 13 |
5 | 00158d0006c9d5ed | 1637970265000 | 2021-11-27 08:44:25 | a2_sen_cds_01 | 0134f2c3 | reported | 6 | 1 | 1 | 2021 | 11 | 27 | 8 |
6 | 00158d0006c9d5ed | 1637965751000 | 2021-11-27 07:29:11 | a2_sen_cds_01 | 83daa627 | reported | 6 | 1 | 1 | 2021 | 11 | 27 | 7 |
7 | 00158d0006c9d5ed | 1637939559000 | 2021-11-27 00:12:39 | a2_sen_cds_01 | 4278c321 | reported | 6 | 1 | 1 | 2021 | 11 | 27 | 0 |
8 | 00158d0006c9d5ed | 1637917262000 | 2021-11-26 18:01:02 | a2_sen_cds_01 | 7a458c00 | reported | 6 | 1 | 1 | 2021 | 11 | 26 | 18 |
9 | 00158d0006c9d5ed | 1637915754000 | 2021-11-26 17:35:54 | a2_sen_cds_01 | 278d125a | reported | 6 | 1 | 6 | 2021 | 11 | 26 | 17 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM temperature_humidity where device_id = '00158d00028d93d8' order by timestamp desc limit 10 /*3회의실온습도센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | hum | temp | battery | status | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d00028d93d8 | 1638146954000 | 2021-11-29 09:49:14 | a2_sen_th_01 | fdc93e9c | reported | 26 | 249 | 0 | 1 | 2021 | 11 | 29 | 9 |
1 | 00158d00028d93d8 | 1638146954000 | 2021-11-29 09:49:14 | a2_sen_th_01 | a6fa9718 | reported | 25 | 249 | 0 | 1 | 2021 | 11 | 29 | 9 |
2 | 00158d00028d93d8 | 1638146653000 | 2021-11-29 09:44:13 | a2_sen_th_01 | 19295a0d | reported | 26 | 244 | 0 | 1 | 2021 | 11 | 29 | 9 |
3 | 00158d00028d93d8 | 1638146362000 | 2021-11-29 09:39:22 | a2_sen_th_01 | 5fc92dc4 | reported | 27 | 240 | 0 | 1 | 2021 | 11 | 29 | 9 |
4 | 00158d00028d93d8 | 1638146057000 | 2021-11-29 09:34:17 | a2_sen_th_01 | 6393c2bc | reported | 27 | 234 | 0 | 1 | 2021 | 11 | 29 | 9 |
5 | 00158d00028d93d8 | 1638146056000 | 2021-11-29 09:34:16 | a2_sen_th_01 | 5f518e6c | reported | 28 | 234 | 0 | 1 | 2021 | 11 | 29 | 9 |
6 | 00158d00028d93d8 | 1638145756000 | 2021-11-29 09:29:16 | a2_sen_th_01 | 87312dd5 | reported | 28 | 227 | 0 | 1 | 2021 | 11 | 29 | 9 |
7 | 00158d00028d93d8 | 1638145751000 | 2021-11-29 09:29:11 | a2_sen_th_01 | 78ccec5f | reported | 29 | 227 | 0 | 1 | 2021 | 11 | 29 | 9 |
8 | 00158d00028d93d8 | 1638145451000 | 2021-11-29 09:24:11 | a2_sen_th_01 | 2967d0b3 | reported | 29 | 219 | 0 | 1 | 2021 | 11 | 29 | 9 |
9 | 00158d00028d93d8 | 1638145150000 | 2021-11-29 09:19:10 | a2_sen_th_01 | 7d5f4627 | reported | 29 | 211 | 0 | 1 | 2021 | 11 | 29 | 9 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM motion_door where device_id = '00158d0002d545b4' order by timestamp desc limit 10 /*3회의실모션센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0002d545b4 | 1638250408000 | 2021-11-30 14:33:28 | a2_sen_mt_01 | 72e2d749 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 14 |
1 | 00158d0002d545b4 | 1638248702000 | 2021-11-30 14:05:02 | a2_sen_mt_01 | 7f6500ba | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 14 |
2 | 00158d0002d545b4 | 1637913814000 | 2021-11-26 17:03:34 | a2_sen_mt_01 | 7374a99b | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 17 |
3 | 00158d0002d545b4 | 1637906976000 | 2021-11-26 15:09:36 | a2_sen_mt_01 | fc6ef1ed | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 15 |
4 | 00158d0002d545b4 | 1637905314000 | 2021-11-26 14:41:54 | a2_sen_mt_01 | 3fff6d7f | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 14 |
5 | 00158d0002d545b4 | 1637904052000 | 2021-11-26 14:20:52 | a2_sen_mt_01 | df21c92d | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 14 |
6 | 00158d0002d545b4 | 1637885207000 | 2021-11-26 09:06:47 | a2_sen_mt_01 | 54b9e9b2 | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 9 |
7 | 00158d0002d545b4 | 1637882804000 | 2021-11-26 08:26:44 | a2_sen_mt_01 | f156880a | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 8 |
8 | 00158d0002d545b4 | 1637882445000 | 2021-11-26 08:20:45 | a2_sen_mt_01 | 622e4ab9 | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 8 |
9 | 00158d0002d545b4 | 1637881650000 | 2021-11-26 08:07:30 | a2_sen_mt_01 | b5c40bab | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 8 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM motion_door where device_id = '00158d0005bb96f3' order by timestamp desc limit 10 /*3회의실문열림센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0005bb96f3 | 1638250388000 | 2021-11-30 14:33:08 | a2_sen_dor_01 | 5eb49bb8 | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 14 |
1 | 00158d0005bb96f3 | 1638146963000 | 2021-11-29 09:49:23 | a2_sen_dor_01 | 0ee044e5 | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 9 |
2 | 00158d0005bb96f3 | 1638145524000 | 2021-11-29 09:25:24 | a2_sen_dor_01 | bced6030 | reported | 0 | 1 | 0 | 2021 | 11 | 29 | 9 |
3 | 00158d0005bb96f3 | 1638145260000 | 2021-11-29 09:21:00 | a2_sen_dor_01 | dcc754bc | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 9 |
4 | 00158d0005bb96f3 | 1638145039000 | 2021-11-29 09:17:19 | a2_sen_dor_01 | 5ee6843e | reported | 0 | 1 | 0 | 2021 | 11 | 29 | 9 |
5 | 00158d0005bb96f3 | 1638145035000 | 2021-11-29 09:17:15 | a2_sen_dor_01 | 274ec676 | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 9 |
6 | 00158d0005bb96f3 | 1638144390000 | 2021-11-29 09:06:30 | a2_sen_dor_01 | ffb89511 | reported | 0 | 1 | 0 | 2021 | 11 | 29 | 9 |
7 | 00158d0005bb96f3 | 1638142790000 | 2021-11-29 08:39:50 | a2_sen_dor_01 | cc1f5fdb | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 8 |
8 | 00158d0005bb96f3 | 1638142526000 | 2021-11-29 08:35:26 | a2_sen_dor_01 | 16e525f4 | reported | 0 | 1 | 0 | 2021 | 11 | 29 | 8 |
9 | 00158d0005bb96f3 | 1637913816000 | 2021-11-26 17:03:36 | a2_sen_dor_01 | 7c29c17d | reported | 0 | 1 | 1 | 2021 | 11 | 26 | 17 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM illumination where device_id = '00158d0005a9998b' order by timestamp desc limit 10 /*연구소조도센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0005a9998b | 1638246272000 | 2021-11-30 13:24:32 | a2_sen_cds_01 | 93f9369c | reported | 0 | 1 | 9 | 2021 | 11 | 30 | 13 |
1 | 00158d0005a9998b | 1638244831000 | 2021-11-30 13:00:31 | a2_sen_cds_01 | 601c4eea | reported | 0 | 1 | 10 | 2021 | 11 | 30 | 13 |
2 | 00158d0005a9998b | 1638244822000 | 2021-11-30 13:00:22 | a2_sen_cds_01 | 7abd863e | reported | 0 | 1 | 9 | 2021 | 11 | 30 | 13 |
3 | 00158d0005a9998b | 1638244791000 | 2021-11-30 12:59:51 | a2_sen_cds_01 | ae4e0806 | reported | 0 | 1 | 4 | 2021 | 11 | 30 | 12 |
4 | 00158d0005a9998b | 1638244781000 | 2021-11-30 12:59:41 | a2_sen_cds_01 | 129de4ae | reported | 0 | 1 | 5 | 2021 | 11 | 30 | 12 |
5 | 00158d0005a9998b | 1638244499000 | 2021-11-30 12:54:59 | a2_sen_cds_01 | cc8ff83e | reported | 0 | 1 | 4 | 2021 | 11 | 30 | 12 |
6 | 00158d0005a9998b | 1638244489000 | 2021-11-30 12:54:49 | a2_sen_cds_01 | e821a0ed | reported | 0 | 1 | 3 | 2021 | 11 | 30 | 12 |
7 | 00158d0005a9998b | 1638244479000 | 2021-11-30 12:54:39 | a2_sen_cds_01 | 147ee512 | reported | 0 | 1 | 4 | 2021 | 11 | 30 | 12 |
8 | 00158d0005a9998b | 1638244419000 | 2021-11-30 12:53:39 | a2_sen_cds_01 | b9431dfe | reported | 0 | 1 | 3 | 2021 | 11 | 30 | 12 |
9 | 00158d0005a9998b | 1638244358000 | 2021-11-30 12:52:38 | a2_sen_cds_01 | 7d9eb350 | reported | 0 | 1 | 4 | 2021 | 11 | 30 | 12 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM motion_door where device_id = '00158d0002cab225' order by timestamp desc limit 10 /*연구소모션센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0002cab225 | 1638248019000 | 2021-11-30 13:53:39 | a2_sen_mt_01 | 1040e7c6 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
1 | 00158d0002cab225 | 1638247475000 | 2021-11-30 13:44:35 | a2_sen_mt_01 | 4af86acf | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
2 | 00158d0002cab225 | 1638246438000 | 2021-11-30 13:27:18 | a2_sen_mt_01 | 32b6aa68 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
3 | 00158d0002cab225 | 1638233537000 | 2021-11-30 09:52:17 | a2_sen_mt_01 | da53ea05 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 9 |
4 | 00158d0002cab225 | 1638231066000 | 2021-11-30 09:11:06 | a2_sen_mt_01 | 472686d4 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 9 |
5 | 00158d0002cab225 | 1638230419000 | 2021-11-30 09:00:19 | a2_sen_mt_01 | 9018cbfd | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 9 |
6 | 00158d0002cab225 | 1638227099000 | 2021-11-30 08:04:59 | a2_sen_mt_01 | 9b7710a8 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 8 |
7 | 00158d0002cab225 | 1638217727000 | 2021-11-30 05:28:47 | a2_sen_mt_01 | 4805e5b3 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 5 |
8 | 00158d0002cab225 | 1638207705000 | 2021-11-30 02:41:45 | a2_sen_mt_01 | de5f7e04 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 2 |
9 | 00158d0002cab225 | 1638177874000 | 2021-11-29 18:24:34 | a2_sen_mt_01 | fa93d91d | reported | 0 | 1 | 1 | 2021 | 11 | 29 | 18 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM motion_door where device_id = '00158d0005bb968a' order by timestamp desc limit 10 /*연구소문열림센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0005bb968a | 1638247519000 | 2021-11-30 13:45:19 | a2_sen_dor_01 | c4f0d490 | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
1 | 00158d0005bb968a | 1638247514000 | 2021-11-30 13:45:14 | a2_sen_dor_01 | 24500391 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
2 | 00158d0005bb968a | 1638247483000 | 2021-11-30 13:44:43 | a2_sen_dor_01 | a1f435ab | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
3 | 00158d0005bb968a | 1638247479000 | 2021-11-30 13:44:39 | a2_sen_dor_01 | 26db99c3 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
4 | 00158d0005bb968a | 1638246665000 | 2021-11-30 13:31:05 | a2_sen_dor_01 | df9ef5b4 | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
5 | 00158d0005bb968a | 1638246662000 | 2021-11-30 13:31:02 | a2_sen_dor_01 | 2fa18fc4 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
6 | 00158d0005bb968a | 1638246449000 | 2021-11-30 13:27:29 | a2_sen_dor_01 | c8ad680b | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
7 | 00158d0005bb968a | 1638246447000 | 2021-11-30 13:27:27 | a2_sen_dor_01 | 92f56584 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
8 | 00158d0005bb968a | 1638246130000 | 2021-11-30 13:22:10 | a2_sen_dor_01 | ee0271a4 | reported | 0 | 1 | 0 | 2021 | 11 | 30 | 13 |
9 | 00158d0005bb968a | 1638246126000 | 2021-11-30 13:22:06 | a2_sen_dor_01 | 4aaeaed6 | reported | 0 | 1 | 1 | 2021 | 11 | 30 | 13 |
df = wr.athena.read_sql_query(
sql="SELECT * FROM temperature_humidity where device_id = '00158d0006c9d545' order by timestamp desc limit 10 /*연구소온습도센서*/;",
database="ambt_b2c"
)
df
device_id | sk | timestamp | model | id | e_type | hum | temp | battery | status | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d00049b8ff3 | 1637558253000 | 2021-11-22 14:17:33 | a2_sen_th_01 | 683fa131 | reported | 29 | 226 | 0 | 1 | 2021 | 11 | 22 | 14 |
1 | 00158d00049b8ff3 | 1637557952000 | 2021-11-22 14:12:32 | a2_sen_th_01 | 2ac12530 | reported | 29 | 228 | 0 | 1 | 2021 | 11 | 22 | 14 |
2 | 00158d00049b8ff3 | 1637557952000 | 2021-11-22 14:12:32 | a2_sen_th_01 | 42741e86 | reported | 30 | 228 | 0 | 1 | 2021 | 11 | 22 | 14 |
3 | 00158d00049b8ff3 | 1637557350000 | 2021-11-22 14:02:30 | a2_sen_th_01 | 24c99ff6 | reported | 30 | 229 | 0 | 1 | 2021 | 11 | 22 | 14 |
4 | 00158d00049b8ff3 | 1637556749000 | 2021-11-22 13:52:29 | a2_sen_th_01 | 413bb2a0 | reported | 30 | 228 | 0 | 1 | 2021 | 11 | 22 | 13 |
5 | 00158d00049b8ff3 | 1637556147000 | 2021-11-22 13:42:27 | a2_sen_th_01 | 6ea69ba3 | reported | 29 | 228 | 0 | 1 | 2021 | 11 | 22 | 13 |
6 | 00158d00049b8ff3 | 1637555846000 | 2021-11-22 13:37:26 | a2_sen_th_01 | 3f9e8043 | reported | 29 | 227 | 0 | 1 | 2021 | 11 | 22 | 13 |
7 | 00158d00049b8ff3 | 1637554943000 | 2021-11-22 13:22:23 | a2_sen_th_01 | bb276dda | reported | 29 | 226 | 0 | 1 | 2021 | 11 | 22 | 13 |
8 | 00158d00049b8ff3 | 1637554642000 | 2021-11-22 13:17:22 | a2_sen_th_01 | 14445641 | reported | 29 | 225 | 0 | 1 | 2021 | 11 | 22 | 13 |
9 | 00158d00049b8ff3 | 1637553739000 | 2021-11-22 13:02:19 | a2_sen_th_01 | 5ccc4310 | reported | 28 | 225 | 0 | 1 | 2021 | 11 | 22 | 13 |
import datetime
datetimeobj = datetime.datetime.fromtimestamp(1638227325000/1000)
datetimeobj
datetime.datetime(2021, 11, 30, 8, 8, 45)
from datetime import datetime
datetime.now().hour
if datetime.now().weekday() < 5 :
if datetime.now().hour > 8 and datetime.now().hour < 17 :
print ('working time')
a = datetime(datetime.now().year, datetime.now().month, datetime.now().day, datetime.now().hour)
b = datetime(int(df['year'][0]), int(df['month'][0]), int(df['day'][0]), int(df['hour'][0]))
print('{}'.format(b))
2021-11-25 13:00:00
if a!=b:
print('not same')
not same
df.head(1)['hour'][0]
'13'
df['year'][0]
'2021'
a = datetime(datetime.now().year, datetime.now().month, datetime.now().day, datetime.now().hour)
b = datetime(int(df['year'][0]), int(df['month'][0]), int(df['day'][0]), int(df['hour'][0]))
if a!=b:
print('same')
import awswrangler as wr
df = wr.s3.read_parquet("s3://hdci-prototyping/motion_door/", dataset=True)
df
device_id | sk | timestamp | model | id | e_type | battery | status | evt | year | month | day | hour | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 00158d0005bb968a | 1633822004000 | 2021-10-10 08:26:44 | a2_sen_dor_01 | b087f0a8 | reported | 0 | 1 | 0 | 2021 | 10 | 10 | 8 |
1 | 00158d0002cab225 | 1634000451000 | 2021-10-12 10:00:51 | a2_sen_mt_01 | c6a1edc2 | reported | 0 | 1 | 0 | 2021 | 10 | 12 | 10 |
2 | 00158d0002cab225 | 1634000825000 | 2021-10-12 10:07:05 | a2_sen_mt_01 | 7b442366 | reported | 0 | 1 | 1 | 2021 | 10 | 12 | 10 |
3 | 00158d0002cab225 | 1634000886000 | 2021-10-12 10:08:06 | a2_sen_mt_01 | ab3c5929 | reported | 0 | 1 | 0 | 2021 | 10 | 12 | 10 |
4 | 00158d0002cab225 | 1634003953000 | 2021-10-12 10:59:13 | a2_sen_mt_01 | 0cdda406 | reported | 0 | 1 | 1 | 2021 | 10 | 12 | 10 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
8269 | 00158d0006c9d665 | 1631145728000 | 2021-09-09 09:02:08 | a2_sen_dor_01 | 2a101ef0 | reported | 6 | 1 | 0 | 2021 | 9 | 9 | 9 |
8270 | 00158d0005bb968a | 1631148819000 | 2021-09-09 09:53:39 | a2_sen_dor_01 | 2838e8cc | reported | 0 | 1 | 1 | 2021 | 9 | 9 | 9 |
8271 | 00158d0005bb96f3 | 1631148863000 | 2021-09-09 09:54:23 | a2_sen_dor_01 | 7af0ae63 | reported | 0 | 1 | 1 | 2021 | 9 | 9 | 9 |
8272 | 00158d0006c9d665 | 1631148869000 | 2021-09-09 09:54:29 | a2_sen_dor_01 | 3d8375e2 | reported | 6 | 1 | 0 | 2021 | 9 | 9 | 9 |
8273 | 00158d0002d545b4 | 1631149065000 | 2021-09-09 09:57:45 | a2_sen_mt_01 | 99fb428f | reported | 0 | 1 | 1 | 2021 | 9 | 9 | 9 |
8274 rows × 13 columns