On Wednesday, September 7, 2016 at 8:32:51 AM UTC-7, Trevor Turk wrote:
>
> Hello,
>
> I ran into a problem where I'm trying to compare two timestamps, one of
> which is an updated_at timestamp that is automatically set when a record is
> marked as processed, to use my app's domain language. So, this query
> returns too many records:
>
> Person.where('updated_at > processed_at')
>
> I believe the problem is caused by me having set processed_at to
> Time.now.utc and letting updated_at be set automatically:
>
> person.update_all(processed_at: Time.now.utc)
>
> I expected the timestamps to match and didn't realize I was dealing with
> microsecond precision. To take an example record:
>
> updated_at: 2016-08-19 22:23:19.391
> processed_at: 2016-08-19 22:23:19.390
>
> I believe the fix would be to compare the timestamps after casting to
> remove milliseconds like this for postgres:
>
> Person.where('updated_at::timestamp(0) > processed_at::timestamp(0)')
>
> ...or like this for all databases:
>
> Person.where('CAST(updated_at AS timestamp(0)) > CAST(processed_at AS
> timestamp(0))').count
>
> Going forward, I believe I can avoid this issue be an issue by explicitly
> setting the two timestamps:
>
> now = Time.now.utc
> person.update_all(processed_at: now, updated_at: now)
>
> ...but I wanted to check in to make sure I'm getting this right, and to
> leave some breadcrumbs for future Googlers who might run into this same
> issue :)
>
In general, I recommend using CURRENT_TIMESTAMP in queries instead of
literal timestamp values representing the current timestamp (which is what
you get if you use Time.now.utc). I'm guessing you are using the
timestamps plugin. I would also recommend using the
current_datetime_timestamp extension on the database, and then using
Person.dataset.current_datetime instead of Time.now. Additionally, you may
want to use the update_refresh plugin to ensure the model's column values
are updated to the database's values after update.
Also, if you are using PostgreSQL, look at having timestamps implemented
using sequel_postgresql_triggers instead of trying to use a model plugin.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
