Menu
I have a postgres table that has the following fields
start_date,duration
duration contains any number of months, so to calculate the end date you add the months in duration to the start date. Now I want to do something like this with a postgres query.
SELECT *
FROM table
WHERE start_date > '2010-05-12'
AND (start_date + duration) < '2010-05-12'
Is this possible and how does one right the syntax?
The version of my postgres is PostgreSQL 8.1.22 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48)
443
To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.
In PostgreSQL, DATE_TRUNC Function is used to truncate a timestamp type or interval type with specific and high level of precision. The datepart argument in the above syntax is used to truncate one of the field,below listed field type: millennium. century.
In PostgreSQL the interval data type is used to store and manipulate a time period. It holds 16 bytes of space and ranging from -178, 000, 000 years to 178, 000, 000 years. It also has additional attribute called “precision (denoted by p)” that can be used to set the level of precision in the query results.
try:
(start_date + (duration * '1 month'::INTERVAL)) < '2010-05-12'
or
(start_date + (duration || ' month')::INTERVAL) < '2010-05-12'
More info: Date/Time Functions and Operators
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
