Search code examples
ruby-on-railspostgresqljsonb

How to get all records with a jsonb field timestamp that matches Sunday or Saturday?

I want to extract all records that match start_date field in json column request to fall in either Saturday or Sunday.

tried this

Foo.where("extract(dow from request->>'start_date') ?| array[5.0, 6.0]")

getting error ERROR: function pg_catalog.date_part(unknown, text) does not exist

Solution

  • You should CAST your value to DATE just before EXTRACT:

    Foo.where("extract(dow from CAST(request->>'start_date' AS DATE)) IN(5.0, 6.0)")