postgres - node-postgres client does not return rows with between clause for dates

by cogitoergosum   Last Updated November 14, 2018 12:06 PM

I have a view created as shown below.

CREATE OR REPLACE VIEW aggr.v_param_data AS
  select date(a.received_timestamp) run_date, a.mId,
  min(a.param_value) min_value, max(a.param_value) max_value, round(avg(a.param_value)::numeric,2) avg_value
  from data.param_data a
  where a.received_timestamp between (current_timestamp - interval '3 months') and current_timestamp
  group by run_date, a.mId

When I run a SELECT against this view in pgAdmin, I get results correctly. For example,

select * from aggr.v_param_data where run_date between '2018-11-01' and '2018-11-14'

However, from my node-postgres based client, I do not get any rows for the same query. This is my snippet.

rdbmsPool.connect(async (err, client, release) => {
    if (err) {
        // Error handling
    } else {
        q = 'select * from aggr.v_param_data where run_date between $1 and $2'
        colValues = [fromDate, toDate] // fromDate = '2018-11-01' and toDate = '2018-11-14' from query string
        client.query(q, colValues, (err, res) => {
            if (err) {
                reject({ 'data': {} })
            } else {
                resolve({ 'data': res.rows }) // res.rows.length = 0

When I remove the between clause, I do get results. The run_date appears as a full time stamp with UTC time zone. So, I tried with setting the colValues array as a full time stamp without timezone. However, that didn't help.

colValues = [mom(fromDate).format('YYYY-MM-DD 00:00:00'), mom(toDate).format('YYYY-MM-DD 23:59:59)]

How do I make a successful SELECT?

mom is required from moment.js.

Answers 1

What Data Type is received_timestamp?

Are you doing date comparisons on Date fields? Your use of format() makes me wonder if you're trying to do Date things with character data, which is always a Bad Idea.

Phill  W.
Phill W.
November 14, 2018 11:39 AM

Related Questions

WHERE clause converting INT to DATETIME

Updated December 15, 2017 15:06 PM