CODEWITHSUNDEEP
pythonpandas
Yas
Yas

Reputation: 97

Is there a function to increment timestamp in Python

We're working with python on an ubuntu 18.04 server, and is storing real time data from temperature sensors on a MySQL database. The database is installed on our server.

What we want to do is to increment a timestamp, where we retrieve the latest value in a 20 min interval, which means that in every 20 min we retrieve the latest temperature value from the sensor, from the MySQL database. We only want the interval to be from .0, 0.20, 0.40.

Example of the incrementing

2019-07-26 00:00:00
2019-07-26 00:20:00
2019-07-26 00:40:00
2019-07-26 01:00:00
...
2019-07-26 23:40:00
2019-07-27 00:00:00
...
2019-07-30 23:40:00
2019-08-01 00:00:00

This is the basic idea of what we want to achieve, but we know this a very bad way of coding thisWe want a more dynamically code. We're imagining that there's a function perhaps, or some other way we haven't thought about. This is what the basic idea looks like:

for x in range (0, 24, 1)
    for y in range (0, 60, 20)
        a = pd.read_sql('SELECT temperature1m FROM Weather_Station WHERE timestamp > "2019-07-26 %d:%d:00" AND timestamp < "2019-07-26 %d:%d:00" ORDER BY timestamp DESC LIMIT 1' % (x, y, x, y+20), conn).astype(float).values

On our database we can retrieve first and last timestamp on our sensor. 

lastLpnTime = pd.read_sql('SELECT MAX(timestamp) FROM Raw_Data WHERE topic = "lpn1"', conn).astype(str).values
firstLpnTime = pd.read_sql('SELECT MIN(timestamp) FROM Raw_Data WHERE topic = "lpn1"', conn).astype(str).values

Therefore we imagine that we can say:

From firstLpnTime to lastLpnTime in a 20 min interval from .00, 0.20 or 0.40 do this retrieve data from the MySQL database

but how we do this?

Upvotes: 1

Views: 697

Answers (2)

kederrac
kederrac

Reputation: 17322

if you want to increment your timestamp you can do something like:

from datetime import datetime, timedelta

your_start_date = '2019-07-26 00:00:00'
date = datetime.strptime(your_start_date, '%Y-%m-%d %H:%M:%S')

for i in range(10):
    print(date.strftime('%Y-%m-%d %H:%M:%S'))
    date += increment

output:

# 2019-07-26 00:00:00
# 2019-07-26 00:20:00
# 2019-07-26 00:40:00
# 2019-07-26 01:00:00
# 2019-07-26 01:20:00
# 2019-07-26 01:40:00
# 2019-07-26 02:00:00
# 2019-07-26 02:20:00
# 2019-07-26 02:40:00
# 2019-07-26 03:00:00

Upvotes: 0

Neo
Neo

Reputation: 627

If you load the data in pandas dataframe you can sample them in the desired time periods using pd.resample .

Upvotes: 1

Related Questions