How to parse a Timestamp() with Python?

Question

I am iterating over a dictionary that contains data from a SQL database and I want to count the number of times that user values appear between initial_date and ending_date, however, I am having some problems when I try to parse the Timestamp values. This is the code I have

initial_date = datetime(2017,09,01,00,00,00)
ending_date  = datetime(2017,09,30,00,00,00)

dictionary sample that I got

sample = {'id': 100008222, 'sector name': 'BONGOX', 'site name': 'BONGO', 'region': 'EMEA', 
'open date': Timestamp('2017-09-11 00:00:00'), 'mtti': '16', 'mttr': '1', 'mttc': '2','user':'John D.'},
{'id': 100008234, 'sector name': 'BONGOY', 'site name': 'BONGO', 'region': 'EMEA', 
'open date': Timestamp('2017-09-09 12:05:00'), 'mtti': '1', 'mttr': '14', 'mttc': '7','user':'John D.'}
{'id': 101108234, 'sector name': 'BONGOA', 'site name': 'BONGO', 'region': 'EMEA', 
'open date': Timestamp('2017-09-01 10:00:00'), 'mtti': '1', 'mttr': '12', 'mttc': '1','user':'John C.'}
{'id': 101108254, 'sector name': 'BONGOB', 'site name': 'BONGO', 'region': 'EMEA', 
'open date': Timestamp('2017-09-02 20:00:00'), 'mtti': '2', 'mttr': '19', 'mttc': '73','user':'John C.'}

This is the code that I use to count the number of times user values appear between initial_date and ending_date

from datetime import time, datetime
from collections import Counter 

#This approach does not work  
Counter([li['user'] for li in sample if initial_date < dateutil.parser.parse(time.strptime(str(li.get(
'open date'),"%Y-%m-%d %H:%M:%S") < ending_date])

The code from above does not work because I encountered the error decoding to str: need a bytes-like object, Timestamp found

I have two questions:

How can I parse this Timestamp value that I encountered in these dictionaries?
I read in this post Why Counter is slow that Collections.Counter is a slow method compared to other approaches to count the number of times an item appears. If want to avoid using Counter.Collections, how can I achieve my desired result of counting the number of times user values appear between these dates?

have a look at this: https://medium.com/@eleroy/10-things-you-need-to-know-about-date-and-time-in-python-with-datetime-pytz-dateutil-timedelta-309bfbafb3f7 — MrE, Sep 16 '17 at 00:56
in your case you need to get the string out of the Timestamp object, or better compare actual UTC timestamps, which is much easier — MrE, Sep 16 '17 at 00:59
btw, psycopg converts dates to Datetime objects by default, so did you simply try Counter([li['user'] for li in sample if initial_date < li.get( 'open date') < ending_date]) — MrE, Sep 16 '17 at 01:04
what is Timestamp? where does this come from? is it from your ORM? Which ORM do you use? — MrE, Sep 16 '17 at 20:13
BTW, tried `Counter([li['user'] for li in sample if initial_date < li.get( 'open date') < ending_date])` and got `Cannot compare type 'Timestamp' with type 'date'` — abautista, Sep 16 '17 at 20:15
Check what Timestamp methods are available to convert to either a string or a date or datetime object. — MrE, Sep 16 '17 at 20:16
@stovfl suggests dt = datetime.fromtimestamp(s['open date']) — MrE, Sep 16 '17 at 20:17
I am retrieving data from a SQL Server Database. Timestamp is generally used as a mechanism for version-stamping table rows. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type. — abautista, Sep 16 '17 at 20:18
The solution from @stovl throws the error `an integer is required (got type Timestamp)` — abautista, Sep 16 '17 at 20:19
A possible solution might be to convert this Timestamp to string and then retrieve the data that is between parenthesis. — abautista, Sep 16 '17 at 20:21
ok, so that's what I suspected. You need to figure out to cast a Timestamp into something useful.The easiest way is use your debugger to get access to the Timestamp object, and figure out what methods it has. Or find the documentation for it and get the info from there. I just don't know where this is from and googleing 'Timestamp' leads nowhere. — MrE, Sep 16 '17 at 20:21
re: convert to string: if Timestamp was convertible to string, str(Timestamp) should have worked. — MrE, Sep 16 '17 at 20:22
Indeed, I also googled for `Timestamp` but this lead me to few options. — abautista, Sep 16 '17 at 20:22
what ORM do you use? it must be an object generated by the ORM. is it SQLAlchemy?something else? — MrE, Sep 16 '17 at 20:22

score 1 · Accepted Answer · answered Sep 16 '17 at 20:53

1

use Timestamp.to_datetime() to convert to a datetime object

answered Sep 16 '17 at 20:53

MrE

19,584
12
87
105

https://stackoverflow.com/questions/13703720/converting-between-datetime-timestamp-and-datetime64 – MrE Sep 16 '17 at 20:54

stovfl · Answer 2 · 2017-09-17T10:00:44.453

Question: How can I parse this Timestamp value that I encountered in these dictionaries?

Using class Timestampe from pandas

from pandas import Timestamp

Using Counter()

# Initialize a Counter() object
c = Counter()

# Iterate data
for s in sample:
    # Get a datetime from Timestamp
    dt = s['open date'].to_pydatetime()

    # Compare with ending_date
    if dt < ending_date:
        print('{} < {}'.format(dt, ending_date))

        # Increment the Counter key=s['user']
        c[s['user']] += 1
print(c)

Output:

2017-09-11 00:00:00 < 2017-09-30 00:00:00
2017-09-09 12:05:00 < 2017-09-30 00:00:00
2017-09-01 10:00:00 < 2017-09-30 00:00:00
2017-09-02 20:00:00 < 2017-09-30 00:00:00
Counter({'John C.': 2, 'John D.': 2})

Question: If want to avoid using Counter.Collections, how can I achieve my desired result of counting

Without Counter()

# Initialize a Dict object
c = {}
# Iterate data
for s in sample:
    # Get a datetime from Timestamp
    dt = s['open date'].to_pydatetime()

    # Compare with ending_date
    if dt < ending_date:
        # Add key=s['user'] to Dict if not exists
        c.setdefault(s['user'], 0)

        # Increment the Dict key=s['user']
        c[s['user']] += 1
print(c)

Output:

{'John D.': 2, 'John C.': 2}

Tested with Python: 3.4.2

There is an error. I've got `an integer is required (got type Timestamp)` :( — abautista, Sep 16 '17 at 20:20

How to parse a Timestamp() with Python?

2 Answers2