0

I am trying to do a fairly simply query that involves getting all the records between two dates via a specific column.

The raw SQL works fine in ISQL Firebird:

SELECT * FROM OPS_HEADER
WHERE PB_BOL_DT
BETWEEN '2020-09-01' AND '2020-09-10';

Here is my ActiveRecord Conversion:

OpsHeader.where('pb_bol_dt BETWEEN 2020-09-01 AND 2020-09-10')

This above line gives me this error:

expression evaluation not supported expression evaluation not supported Only one operand can be of type TIMESTAMP

I may be converting it wrong but it sure seems like this is the exact way to do it... I have no idea why it's giving me so much trouble.

AttemptedMastery
  • 738
  • 6
  • 21
  • 1
    Where did the quotes around the date literals go? And why not `OpsHeader.where(pb_bol_dt: '2020-09-01' .. '2020-09-10')` or `OpsHeader.where('pb_bol_dt between ? and ?', '2020-09-01', '2020-09-10')` or `OpsHeader.where('pb_bol_dt between :lower and :upper', lower: '2020-09-01', upper: '2020-09-10')`? – mu is too short Sep 16 '20 at 19:34
  • I've been out of the game so long... this was a good refresher. If you want, put it as an answer below and I will accept. Also, do these all work similarly, with regards to performance? – AttemptedMastery Sep 16 '20 at 20:53
  • 1
    use http://fbprofiler.sf.net to check what your DB actually receives form your app. Firebird 2.5+ (TraceAPI) required – Arioch 'The Sep 18 '20 at 10:49

1 Answers1

2

You're missing quotes on the date literals, you'd want to start with:

OpsHeader.where(%q(pb_bol_dt BETWEEN '2020-09-01' AND '2020-09-10'))

But you can get ActiveRecord to build a BETWEEN by passing it a range:

OpsHeader.where(pb_bol_dt: '2020-09-01' .. '2020-09-10')

and letting AR deal with the quoting. You could also pass the end points separately using positional or named placeholders:

OpsHeader.where('pb_bol_dt between ? and ?', '2020-09-01', '2020-09-10')
OpsHeader.where('pb_bol_dt between :lower and :upper', lower: '2020-09-01', upper: '2020-09-10')

All of these will end up sending the same SQL to the database, the only difference is the small amount of string processing and type handling that is needed to build the latter three queries.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Thanks for answering! Quick question for you; what if I wanted to check between two timestamps of the sameday? Would it look like this? ````pb_status_time: '2020-09-01 00:00:00' .. '2020-09-01 24:00:00')````? – AttemptedMastery Sep 17 '20 at 21:15
  • 1
    That should work, or you could use a range of real time instances. – mu is too short Sep 17 '20 at 23:07