Postgresql – Query to extracting the 14th date from a date after excluding the sunday and saturday in Postgresql

postgresql

I need a query to extract the 14th day or any other day before a day after excluding Saturday and Sunday in Postgresql.

For Example, I need 14th day before 2020-01-30 after excluding Saturday and Sunday.

Date Dimension is " YYYY-MM-DD"

I am using 'ISODOW' < 6 condition to exclude Sunday and Saturday. But from the count, i am not sure how to fetch the date

 extract('ISODOW' FROM the_day) < 6

Best Answer

WITH cand AS (
   SELECT DATE '2020-01-30' - i AS d
   FROM generate_series(1, 2*14+3) AS i
)
SELECT d
FROM cand
WHERE extract(isodow FROM d::timestamp) < 6
ORDER BY d DESC
OFFSET 13
LIMIT 1;

The 2*14+3 in the CTE is a safe upper limit and can be improved.

Related Solutions

PostgreSQL – Storing Historical Records and Extracting Data from a Given Period

The problem is that when an area is merged and its code is reused, you have that information, but when an area is merged and its code is deprecated, you don't have it. In other words, you don't have the information that an area is deprecated.

Now, I see two solutions. First, keep the schemas as is and whenever an area is deprecated, add a new row as it was a new area and just states it is deprecated, e.g.

INSERT INTO myTable(code, area, change_date)
VALUES(86001, 'deprecated', '2015-01-01')

So in your query it will show as 'deprecated' or you filter it out.

A second, and possible best option, is to have a new column that states when (and if) an area was deprecated:

ALTER TABLE myTable ADD deprecated DATE;
UPDATE myTable SET deprecated = '2015-01-01' WHERE gid = 1;

So, on your query you can simple add the filter:

(deprecated IS NULL OR deprecated >= '2016-01-01')

The full code:

SELECT DISTINCT ON (code) code, area, change_date
FROM myTable
WHERE change_date < '2016-01-01'
    AND (deprecated IS NULL OR deprecated >= '2016-01-01')
ORDER BY code, change_date DESC;

As another option for non-deprecated areas, you can set them as 'infinity' instead of NULL, so the query does not need the deprecated IS NULL condition, as 'infinity' will always be higher or equal than any other non-null value.

Postgresql – Extracting dates from PostgreSQL database

Am I making a mistake using jsonb to store the configuration?

Yes. You're querying on it. That's usually an indicator to me that you're abusing JSONB.

A few other notes,

On your jsonb formatting. I've cleaned it up. Using JSON, "4" is not the same as 4. You should store numbers as numbers and not as strings
Your default for configuration is {}, but your sample data shows [{},{}]. It's bad practice to store some arrays-of-objects, and some objects in the top level. Pick one.

Best Answer

Related Solutions

PostgreSQL – Storing Historical Records and Extracting Data from a Given Period

Postgresql – Extracting dates from PostgreSQL database

Related Question