some timestamp queries behave differently

[hschellman]

metacat query "files from dune:all where core.file_type='detector' and core.run_type='protodune-sp' and core.data_tier='raw' and core.start_time >= datetime(2018-10-20) and core.start_time <= datetime(2018-12-31) limit 10"

works fine but

metacat query "files from dune:all where core.file_type='detector' and core.run_type='protodune-sp' and core.data_tier='raw' and created_timestamp >= datetime(2018-10-20) and created_timestamp <= datetime(2018-12-31) limit 10" MQLExecutionError: operator does not exist: timestamp with time zone >= numeric LINE 17: and ( f_1.created_timestamp >= 1539993600.0 ... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

is there a type issue with created_timestamp versus core.start_time - they look the same (floating point) in the json dump for a file

[ivmfnal]

created_timestamp is not metadata. It is a file attribute as documented here: https://metacat.readthedocs.io/en/latest/concepts.html#file-attributes

Being a file attribute, created_timestamp is stored as a native date/time database type and can be compared like this:

files from dune:all 
  where core.file_type='detector' 
     and core.run_type='protodune-sp' 
     and core.data_tier='raw' 
     and created_timestamp >= '2018-10-20' 
     and created_timestamp <= '2018-12-31' 
     limit 10

[hschellman]

This is partially why I am writing an idiot-proof script for the most common use cases. To get around all the syntax gotchas a novice will encounter. Will code it and see.

On May 4, 2023, at 11:43 AM, Igor Mandrichenko @.**@.>> wrote:

[This email originated from outside of OSU. Use caution with links and attachments.]

created_timestamp is not metadata. It is a file attribute as documented here: https://metacat.readthedocs.io/en/latest/concepts.html#file-attributes

Being a file attribute, created_timestamp is stored as a native date/time database type and can be compared like this:

files from dune:all where core.file_type='detector' and core.run_type='protodune-sp' and core.data_tier='raw' and created_timestamp >= '2018-10-20' and created_timestamp <= '2018-12-31' limit 10

— Reply to this email directly, view it on GitHubhttps://github.com/ivmfnal/metacat/issues/27#issuecomment-1535239283, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AIA37DM5B2CZZREZMUI6U4TXEP2D3ANCNFSM6AAAAAAXWATDGI. You are receiving this because you authored the thread.Message ID: @.***>

[ivmfnal]

Easy way to distinguish between file attributes and metadata is: metadata parameter names always have at least one dot in them separating the parameter category