count_full_weekend_days(date, date)įrom (select CURRENT_DATE + i as Date, EXTRACT(DOW FROM CURRENT_DATE + i) as DiaDateįrom generate_series(date ($1) - CURRENT_DATE, date ($2) - CURRENT_DATE ) i) as MySerieĪfter that, you can use the function to return only the number of weekend days in a interval. Just the way you will have more flexibility to use this function.
#Postgresql datediff code
This code above will create a sql function that count and return the amount of weekend days (Sat, Sun). I suggest you to create a function for use whenever you want and write less ) You can then write your query as a quick count of records from this table. SELECT top 100000 row_number() over (order by o.object_id) as number Once the table is created, you should be able to populate it with date data relatively quickly. You can quickly create one like so: CREATE TABLE. The method I would use would be to make sure that a table of dates is available. There are a couple of things you can do to make this easier. Typically, you might include the lower and exclude the upper bound: SELECT count(*) AS count_days_no_weekend To exclude lower and / or upper bound, add / subtract 1 day accordingly.
#Postgresql datediff full
The upper bound is only excluded if the last interval would be truncated, which is not the case with full days. Note in particular that generate_series() includes the upper bound in the output, as long as a full interval (3rd parameter) fits. SRF (set returning functions), also referred to as "table-functions", can be used just like tables in the FROM clause. You don't need an extra subquery level for generate_series(). WHERE extract('ISODOW' FROM the_day) < 6 Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler: SELECT count(*) AS count_days_no_weekend
0 kommentar(er)
