[ome-devel] Timestamp over HQL

[Josh Moore]

Hi Douglas,

On Thu, Jan 11, 2018 at 8:07 PM, Douglas Russell
<douglas_russell at hms.harvard.edu> wrote:
> Hi,
>
> I noticed something weird when getting timestamps using HQL:
>
> If I use the `omero hql` functionality I get this:
>
> (virtualenv) [omero-local at itlomerop01 ~]$ omero_server hql "SELECT event.id,
> event.time FROM Event event WHERE id=4621437"
>
>  # | Col1    | Col2
> ---+---------+--------------------------
>  0 | 4621437 | Mon Jan 23 14:12:45 2017
> (1 row)
>
> Looks correct, and also this is what I see when I use the webclient.

This is indeed handled by the client code:

    elif isinstance(object, RTimeI):
        rv = time.ctime(unwrapped/1000.0)

See https://github.com/openmicroscopy/openmicroscopy/blob/develop/components/tools/OmeroPy/src/omero/plugins/hql.py#L188


> Also, if I query the database directly I get the same date and if I ask the
> database to convert to seconds from epoch, I get what I expect to see:
>
> select extract(epoch from event.time) from event where id = 4621437;
>    date_part
> ----------------
>  1485180765.782
>
> However, if I do a projection HQL query the response I get is:

## HQL: SELECT event.id, event.time ...
 0  | 0    | Mon Sep 30 13:00:26 2013

## CODE: ...projection("select e.time...

In [3]: rv[0][0].val
Out[3]: 1380542426133L

In [4]: l = rv[0][0].val

In [5]: l2 = l/1000.0

In [6]: import time

In [7]: time.ctime(l2)
Out[7]: 'Mon Sep 30 13:00:26 2013'

## PSQL: select extract(epoch ...

1380546026.13309


I'm pretty what your seeing is a timezone handling issue. cF:

# select extract(epoch from cast(event.time as timestamp with time
zone)) from event where id =0;
    date_part
------------------
 1380542426.13309
(1 row)

See https://stackoverflow.com/questions/29536542/different-results-for-extract-epoch-on-different-postgresql-servers
for more info.

Cheers,
~Josh


> Event ID, Event Time
> 4621437 1428347867334
>
> What is 1428347867334? It's not seconds/milliseconds/microseconds/etc from
> epoch.
>
> The same is true when trying to query over a given range using an HQL
> projection, which is ultimately what I was trying to achieve (a projection
> where I use several criteria including the event timestamp).
>
> Cheers,
> Douglas