CODEWITHSUNDEEP
pythonmysqldatetime
blade_runner
blade_runner

Reputation: 145

Datetime tuple to seconds tuple

What's the best way to convert a tuple of 90'000 datetime.datetime objects which I get from the query below to a tuple of seconds since midnight?

e.g. 2014-06-13 10:33:20 should become 38000 (10*3600+33*60+20)

query = """
        SELECT timestamp, value
        FROM measurements
        WHERE timestamp BETWEEN %s AND %s AND sensor = %s 
        """
cursor.execute(query, (start, stop, sensor))
row = cursor.fetchall()
timestamp, value=zip(*row)

Upvotes: 2

Views: 383

Answers (4)

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

SELECT TIME_TO_SEC(DATE_FORMAT(your_date,'%H:%i:%s')) AS SECOND
FROM TABLE_NAME

Check TIME_TO_SEC and DATE_FORMAT function

Upvotes: 0

tktk
tktk

Reputation: 11734

You might want to select the value in seconds to begin with:

query = """
    SELECT TIMESTAMPDIFF(SECOND,DATE(timestamp),timestamp), value
    FROM measurements
    WHERE timestamp BETWEEN %s AND %s AND sensor = %s 
    """

the TIMESTAMPDIFF is there so you'll get only the time part in seconds and not the entire date.

Upvotes: 0

Ffisegydd
Ffisegydd

Reputation: 53678

Create a function to convert to seconds and then use a list comprehension.

from datetime import datetime, timedelta
import random

# Generate some random datetime objects.
d = [datetime.today() + timedelta(seconds=i*600) for i in range(10)]

def dt_to_seconds(dt):
    return 3600*dt.hour + 60*dt.minute + dt.second

s = tuple([dt_to_seconds(i) for i in d])

print(s)
# (35545, 36145, 36745, 37345, 37945, 38545, 39145, 39745, 40345, 40945)

Upvotes: 3

sundar nataraj
sundar nataraj

Reputation: 8692

consider z consists of list of your time values then map()

z=["10:33:20","4:50:22"]  

print map(lambda x:int(x.split(':')[0])*3600+int(x.split(':')[1])*60+int(x.split(':')[2]),z)
#output[38000, 17422]

Upvotes: 0

Related Questions