Split function in PostgreSQL (PostgreSQL window function)

In this post I will show you how to get date as ‘from date’ and ‘to date’ from the same column in table. We can use split function in PostgreSQL (PostgreSQL window function) to split column into two. First we will create a new table for our example. We can use pgAdmin for creating tables and executing query, as it gives an easy GUI for development. If you don’t know how to create database and tables using pgAdmin first read the post here.

We will use the same database described in the post in this example also. Now right click on the ‘Tables’ menu and select ‘New Table’. Enter table name as ‘tax’. Now right click on the columns and select ‘New Column’. First create the column ‘id’ with data type serial and then create columns ‘percentage’ (numeric),’changed_date’ (timestamp without time zone) and ‘project_id’ (integer) .Now add id as the primary key of the table.

insert into tax(tax,changed_date,project_id)values(1.2,'2015-01-01',1);
insert into tax(tax,changed_date,project_id)values(1.5,'2015-02-01',1);
insert into tax(tax,changed_date,project_id)values(1.3,'2015-03-01',1);
insert into tax(tax,changed_date,project_id)values(1.8,'2015-05-01',1);
insert into tax(tax,changed_date,project_id)values(1.9,'2015-08-01',1);

Below is the query to get tax value within a date range using split function in PostgreSQL (PostgreSQL window function). In the query we are taking the row’s changed date as date_from and next highest date minus one as date_to.

SELECT id, percentage,
changed_date AS date_from,
lead(changed_date- interval '1 day') OVER (PARTITION BY project_id ORDER BY changed_date ) AS date_to, project_id
FROM tax
ORDER BY project_id, changed_date;

This will split the changed_date as date_from and date_to for a particular project.