[ome-devel] Timestamp over HQL

[Douglas Russell]

I would understand it as a timezone issue if it was off by less than a day,
but the event time I'm getting is more than 2 years out for this event.

I just tried it on an OMERO 5.4 and it worked fine, so either it's an
OMERO+, OMERO 5.2, or some other aspect of our environment (although I
doubt this because accessing through mechanisms other than hql projection
seem to work). I'll report this to Glencoe instead.

Cheers,

Douglas



On Fri, 12 Jan 2018 at 08:13 Josh Moore <josh at glencoesoftware.com> wrote:

> 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
> _______________________________________________
> ome-devel mailing list
> ome-devel at lists.openmicroscopy.org.uk
> http://lists.openmicroscopy.org.uk/mailman/listinfo/ome-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openmicroscopy.org.uk/pipermail/ome-devel/attachments/20180112/718ea493/attachment.html>