Fixing ERROR: Date/Time Field Value Out of Range in PostgreSQL

postgresql

I am trying to run the Select query(linked server) from SSMS to get the data from PostgreSQL on AWS cloud. My query is running fine in SSMS but as soon as I enter the following line of code

and c.created_date >=concat(to_char(CURRENT_DATE - interval '7 day', 'yyyy-mm-dd'),' 00:00:00'):: timestamp

and c.created_date <= concat(to_char(CURRENT_DATE - interval '1 day','yyyy-mm-dd') ,' 23:59:59') ::timestamp

it starts giving me ERROR: date/time field value out of range: "2020-06-0700:00:00";

created_date field in my PostgreSQL is timestamp without timezone

Which datatype should I chose which is compatible with SQL Server?

Best Answer

You are overcomplicating things. concat() and to_char() are not needed and the root cause of the problem.

The start of the day 7 days back can be obtained using: current_date - 7. As that doesn't have a time part, a comparison with a timestamp column will automatically assume 00:00:00.

If you want to compare everything up to yesterday midnight, simply use < current_date.

So your expression can be simplified to:

and created_date >= current_date - 7
and created_date < current_date

Note the < instead of <= in the second condition.

If you want to be explicit about the time, you could use

and created_date >= (current_date - 7) + time '00:00:00'
and created_date < current_date + time '00:00:00'

But that's not needed and is absolutely identical to the first condition.

created_date < current_date is roughly equivalent to created_date <= (current_date - 1) + time '23:59:59' (which is your initial intention).

However, it's only roughly equivalent because your attempt isn't actually correct as it misses the time between 23:59:59.000000 and 23:59:59.999999, that's why it's better to use < with the next day's start.

Related Solutions

Postgresql – Joining based on date (or date range)

That is going to be horribly messy, since you'll need to find the single (staffid, effectivefrom) pair that's the newest prior to the dateParameter.

What I'd do is add another field effectiveTo to go with effectiveFrom, and then when you add a new target for a staff member, before you add it check and see if there are any current targets (effectiveTo IS NULL) and if there are then set their end date to the new target start date (optionally: throw an error if there's more than one current target).

So, the table turns out looking like:

 staffID | target | effectiveFrom | effectiveTo
 --------|--------|---------------|-------------
  1      |  6.0   | 2012-01-01    | 2012-03-01
  2      |  6.0   | 2012-01-01    | NULL
  3      |  6.0   | 2012-01-01    | NULL
  1      |  7.0   | 2012-03-01    | NULL

That makes your search query look like this:

SELECT  othertable.*, targets.target
FROM    othertable
            INNER JOIN 
        targets ON othertable.staffid = targets.staffid
WHERE dateParameter > effectiveFrom AND (dateParameter <= effectiveTo OR effectiveTo IS NULL)

The potential downside of that is data inconsistency (i.e. staff members having overlapping targets) - you can fairly easily write a script to check that though. I'd recommend making an addTarget(staffID, target, fromDate) stored procedure and wrapping all the checking/updating logic in that, also.

(A couple of style notes - I rewrote your query slightly to seperate the join condition from the where clause. Also, I know it's only an example, but SELECT table.* is a bad habit to get into - you land up transferring far more data than you need.)

PostgreSQL – Understanding ‘ERROR: Tuple Offset Out of Range: 0’

It was all caused by the bug described and fixed in this release: http://www.postgresql.org/about/news/1506/

It caused us a lot of trouble!

Best Answer

Related Solutions

Postgresql – Joining based on date (or date range)

PostgreSQL – Understanding ‘ERROR: Tuple Offset Out of Range: 0’

Related Question