Query versions within date range

Pipeline Integrations
1

I’m struggling to understand why this is not querying the versions between the two given dates? I’m using python. what am i doing wrong here?

My goal is to query all versions within a start and end date. I personally don’t care about the hour, minute, second. Just the start of one day and the end of the second day. Give me all the verions :slight_smile:

def queryVersions():
    filters = [
        ['project', 'is', {'type': 'Project', 'id': projectId}],
        ['updated_at', 'between',
            [
                datetime.datetime(2020,5,14,0,0,0).strftime('%Y-%m-%dT%H:%M:%SZ'),
                datetime.datetime(2020,5,15,1,0,0).strftime('%Y-%m-%dT%H:-%M:%SZ')
            ]
        ],
        ['user', 'is', {'type': 'HumanUser', 'id': 441}] # matt
    ]
    results = sg.find('Version', filters, ['code', 'user', 'updated_at'])
    print len(results)
    pprint.pprint(results, indent=3)

I tried this and it also does not work…

def queryVersions():
    startDate = datetime(2020, 5, 14, 0, 0, 0).utcnow()
    endDate = datetime(2020, 5, 15, 0, 0, 0).utcnow()
    print startDate, endDate

    filters = [
        ['project', 'is', {'type': 'Project', 'id': projectId}],
        ['updated_at', 'between',
            [
                startDate,
                endDate
            ]
        ],
        ['user', 'is', {'type': 'HumanUser', 'id': 441}] # matt lef
    ]
    results = sg.find('Version', filters, ['code', 'user', 'updated_at'])
    print len(results)
    pprint.pprint(results, indent=3)
1 Like
3

Hi there!

Your code looks correct. Does it work the way you expect if you change your strftime format string to something like %Y-%m-%dT%H:%M:%S.%f%z? In your example, you’re using %Y-%m-%dT%H:%M:%SZ, which is an indicator for UTC time, but by default, the API is set to convert (OS-provided) local time to UTC time and back, as you can see here: https://developer.shotgunsoftware.com/python-api/reference.html#shotgun

If that’s the issue, I’d suggest changing your format string as above, and querying for local time. Conversely, you could flip the convert_datetimes_to_utc flag to False and try it that way, but I’m less sure of the results.

Above, I’m assuming that you’re getting zero results, but I don’t know that for sure from your information – if that’s not the case, I think we might need some more details. It’d be nice to know exactly how your example isn’t working. Is it returning zero results? Is it crashing? If so, what’s the error? If you’re not getting any results, what happens if you create the same filter in the UI? Does it work? Is there a Version that you know you should find but the filter isn’t returning?

One other note: I see that you’re filtering for date_updated; if the version was created a long time ago but not updated recently, it wouldn’t show up in your results – just checking in case you wanted date_created instead, but I’m guessing you’re already sure about that.

3 Likes
5

Heya, stubbled uponm that old question while trying to do the same thing. I know this ridiculously old, might have been solved in the api itself by then, but anywho… The way I managed to make it work is by using a timezone argument like so:

from shotgun_api3.lib.sgtimezone import LocalTimezone
timezone = LocalTimezone()
start = datetime.datetime(2024, 11, 25, 0, 0, 0, tzinfo=timezone)
end = datetime.datetime(2024, 11, 27, 0, 0, 0, tzinfo=timezone)
filters = [["updated_at", "between",[start, end]]]
fields = list() # whatever you need to query
versions = sg.find("Version", filters, fields)

hope that helps

6

Correct, I believe Sg database stores time in UTC-8 so your query needs to include a timezone to work properly if you are not in the UTc-8 timezone.