Author Topic: How to query files based on the Date Taken  (Read 2777 times)

0 Members and 1 Guest are viewing this topic.

Bernie

  • Member
  • **
  • Posts: 7
    • View Profile
How to query files based on the Date Taken
« on: 2022-06-04 05:05:02 »
Hi,

Searching (quering) feature is great.

Let consider I see following in My Metadata pane
Info > Date Taken : display 31/05/2022 13:59:50.825

How could I search all files from that same 'Date Taken' date : 31/05/2022 ?

I did try various combinaison like:
 tn.[taken_time_iso] like '2022:%:% %:%:%'
or
 tn.[taken_time_iso] = '31/05/2022 13:59:50.825'
in query Advanced pane but none give me results!

Thanks in advance
Regards
Bernie

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
Re: How to query files based on the Date Taken
« Reply #1 on: 2022-06-04 08:01:30 »
Most dates and times in the database are stored in unix format, i.e. seconds that have passed since jan 1, 1970. You can use the Udate command to convert them into the less cryptic ISO format that Windows uses. This is a sample query that queries the regular file date:

    tn.file_time = ^udate(yyyy-mm-dd HH:mm:ss)

For a range of times/dates, you'd combine two expressions using the AND operator, like this:

    (tn.file_time >= ^udate(2022-02-10))
    AND (tn.file_time < ^udate(2022-02-11))

Hopefully these examples will also work when you use the  tn.[taken_time_iso] field instead.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Bernie

  • Member
  • **
  • Posts: 7
    • View Profile
Re: How to query files based on the Date Taken
« Reply #2 on: 2022-06-10 13:33:09 »
Hi,

Many Thanks!
I finally managed searching files using following syntax:
( tn.[taken_time_iso] >  '2022-05-07 00:00:00') AND ( tn.[taken_time_iso] <  '2022-05-08 00:00:00')

Regards
Bernard

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
Re: How to query files based on the Date Taken
« Reply #3 on: 2022-06-10 20:10:53 »
Thanks Bernard! It's interesting that no conversion is required for the taken_time_iso field!
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.