Date Column Arithmetic in Postgresql Query

Date column arithmetic in PostgreSQL query

You would need to table-qualify t1.user_id to disambiguate. Plus other adjustments:

CREATE TABLE foo AS 
SELECT user_id, (t1.the_date - (t2.the_date - t1.the_date)) AS start_date
FROM   table1 t1
JOIN   table2 t2 USING (user_id);

• Subtracting two dates yields integer. Cast was redundant.

• Don't omit the AS keyword for column aliases - while it's generally OK to omit AS for table aliases. The manual:

  You can omit AS, but only if the desired output name does not match
  any PostgreSQL keyword (see Appendix C). For protection against
  possible future keyword additions, it is recommended that you always
  either write AS or double-quote the output name.)

• Joining tables with a USING clause only keeps one instance of the joining columns(s) (user_id in this case) in the result set and you don't have to table-qualify it any more.

Date arithmetic on values of type DATE without resulting in TIMESTAMP

You already found that you can add integer to date to add days, returning date:

SELECT due_date + 7 AS due_date_mod  -- type date
FROM   test_table;

(Since date is an integer quantity inside, it's dead simple and cheap to add integer to it.)

As you can see in the table Date/Time Operators in the manual, there is no other operator that would return date (and no function, either).

And the unit "months" must be interpreted relative to the left operand (as months differ in their number of days). So you cannot simply extract the number of days from your months. You must use date + interval to get it right. Just add a cast, it's simple and very cheap:

SELECT (due_date + interval '3 months')::date AS due_date_mod
FROM   test_table;

You are aware that (7 * interval '1 mon') = interval '7 mon', right?

Fetch records of current month using PostgreSQL query

You can compare the month and year of a date with the current one. But the index by field will not be used, you can build a separate index by year and month for this.

select *
from your_table
where extract(YEAR FROM createdAt) = extract(YEAR FROM now())
      and extract(MONTH FROM createdAt) = extract(MONTH FROM now())

SQL (postgresql) update a date column using an interval from data in a join table

You can join the tables and multiply the value of the column months by INTERVAL '1 month' in the UPDATE statement:

UPDATE users AS u
SET new_date = u.date + INTERVAL '1 month' * p.months
FROM plans AS p
WHERE p.id = u.plan_id;

See the demo.

Converting Oracle date arithmetic to PostgreSQL

If you want to do time subtraction, I would use EXTRACT function.

EXTRACT(SECOND FROM now() - sm.last_sent_date)  * 24 * 60 * 60 > sm.delay

We can see now()- '2000.01.01'::timestamp will return interval not the interger.

so EXTRACT function can help get the amount from time subtraction

Query 1:

SELECT  now()- '2000.01.01'::timestamp,
        EXTRACT(SECOND FROM now() - '2000.01.01'::timestamp) 

Results:

|                                                ?column? | date_part |
|---------------------------------------------------------|-----------|
| 0 years 0 mons 6852 days 8 hours 47 mins 37.710379 secs | 37.710379 |

Date arithmetic using integer values

Wow. I'm surprised, but using the functions from this page - specifically the one to build a date value from three integers - which really do nothing more expose the internal C date functions, really is a lot faster. Benchmarking for me show that creating the dates that way was much faster.

First one is the implementation of the "dateserial" function:

postgres=# select to_date(a,1,3) 
postgres-# from generate_series(100,1000000) as v(a);

Time: 1365.851 ms

postgres=# select (a::text||'-01-03')::date from 
postgres-# generate_series(100,1000000) as v(a);

Time: 3454.224 ms

Full Solution

Edit dateserial.c:

#include "postgres.h"
#include "utils/date.h"
#include "utils/nabstime.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

Datum dateserial(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1 (dateserial);

Datum
dateserial(PG_FUNCTION_ARGS) {
  int32 p_year = PG_GETARG_INT32(0);
  int32 p_month = PG_GETARG_INT32(1);
  int32 p_day = PG_GETARG_INT32(2);

  PG_RETURN_DATEADT( date2j( p_year, p_month, p_day ) - POSTGRES_EPOCH_JDATE );
}

Edit Makefile:

MODULES = dateserial
PGXS := $(shell pg_config --pgxs)
include $(PGXS)

Edit inst.sh (optional):

#!/bin/bash

make clean && make && strip *.so && make install && /etc/init.d/postgresql-8.4 restart

Run bash inst.sh.

Create a SQL function dateserial:

CREATE OR REPLACE FUNCTION dateserial(integer, integer, integer)
  RETURNS date AS
'$libdir/dateserial', 'dateserial'
  LANGUAGE 'c' IMMUTABLE STRICT
  COST 1;
ALTER FUNCTION dateserial(integer, integer, integer) OWNER TO postgres;

Test the function:

SELECT dateserial( 2007::int, 5, 5 )

How do you find results that occurred in the past week?

You want to use interval and current_date:

select * from books where returned_date > current_date - interval '7 days'

This would return data from the past week including today.

Here's more on working with dates in Postgres.

---

Related Topics