Postgresql – Calculating new date with date/time operators fails with variables

datetimepostgresqlpostgresql-9.3

I have two fields, one timestamp (calldate), and one bigint (duration – no idea why it's this big).

Through a query I would like to construct a 3rd field that is the timestamp plus the bigint as seconds.

With 9.3 I don't have access to make_interval, but the functions and operators seem useless.

select calldate + interval + duration || ' seconds' as "completion_date";
select calldate + interval + '900 seconds' as "completion_date";

The above is abbreviated a bit, but the 2nd line works while the 1st line fails. I'm unable to pass or construct the interval in a way it will accept. I was hoping to use the make_interval function that would return an interval type, but alas I'm using 9.3. I've tried many, many, different variations using () to try and isolate it as well as casting values.

Nothing works. How can I add the duration in seconds to a timestamp value?

Best Answer

To construct the interval, multiply the number with the 1 second interval:
duration * interval '1 second'

select calldate + duration * interval '1 second' as "completion_date";

or:

select calldate + duration * '1 second'::interval as "completion_date";

Postgres docs have a page about Date/Time Functions and Operators, where there are similar examples:

Operator    Example                     Result
  *         900 * interval '1 second'   interval '00:15:00'
  *         21 * interval '1 day'       interval '21 days'

Your third query has issues

SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;

ORDER BY published_date DESC, but published_date can be NULL (no NOT NULL constraint). That's a loaded foot-gun if there can be NULL values, unless you prefer NULL values over the latest actual published_date.

Either add a NOT NULL constraint. Always do that for columns that can't be NULL.
Or make that ORDER BY published_date DESCNULLS LAST and adapt the index accordingly.

"articles_user_id_published_date_idx" btree (user_id, published_date DESC NULLS LAST)

Details in this recent, related answer:

Extremely slow query on indexed column in Postgres

Convert `published_date` to an actual `date`

While 'published_date' is always rounded, it's effectively just a date which occupies 4 bytes instead of 8 for the timestamp. You would best move that up in the table definition to come before the two timestamp columns, so you don't lose the 4 bytes to padding:

...
body           | text
published_date | date   --     <---- here
created_at     | timestamp without time zone
updated_at     | timestamp without time zone

Smaller on-disk storage does make a difference for performance.

Configuring PostgreSQL for read performance

More importantly, your index on (user_id, published_date) would now just occupy 32 bytes per index entry instead of 40, because 2x4 bytes do not incur extra padding. And that would make a noticeable difference for performance.

Aside: this index is not relevant to the demonstrated queries. Delete unless indexes unless used elsewhere:

~~"index_articles_on_published_date" btree (published_date)~~

Postgresql – How to deal with datetime ranges in a table with separate columns for date and time

You can use the + operator.

SELECT pk,ev_date,ev FROM events;

 pk |  ev_date   |    ev    
----+------------+----------
  1 | 2016-02-19 | 01:00:00
  2 | 2016-02-19 | 02:00:00
  3 | 2016-02-19 | 05:00:00
  4 | 2016-02-19 | 12:00:00
  5 | 2016-02-19 | 18:00:00
  6 | 2016-02-19 | 23:00:00
  7 | 2016-02-20 | 01:00:00
  8 | 2016-02-20 | 05:00:00
  9 | 2016-02-20 | 12:00:00
 10 | 2016-02-20 | 18:00:00
(10 rows)

SELECT pk, ev_date, ev 
FROM events 
WHERE (ev_date + ev) 
    BETWEEN ('2016-02-19 04:00:00') 
        AND ('2016-02-20 02:00:00');

 pk |  ev_date   |    ev    
----+------------+----------
  3 | 2016-02-19 | 05:00:00
  4 | 2016-02-19 | 12:00:00
  5 | 2016-02-19 | 18:00:00
  6 | 2016-02-19 | 23:00:00
  7 | 2016-02-20 | 01:00:00
(5 rows)

Don't forget to create the index below:

CREATE INDEX events_ts_idx ON events ((ev_date + ev));
ANALYZE events;

I've inserted many dummy rows, so I show the result of EXPLAIN:

EXPLAIN ANALYZE SELECT pk, ev_date, ev FROM events  WHERE (ev_date + ev) 
    BETWEEN ('2016-02-19 23:50:00') 
        AND ('2016-02-20 00:01:00');
                                                                            QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using events_ts_idx on events  (cost=0.29..8.52 rows=8 width=16) (actual time=0.014..0.029 rows=42 loops=1)
   Index Cond: (((ev_date + ev) >= '2016-02-19 23:50:00'::timestamp without time zone) AND ((ev_date + ev) <= '2016-02-20 00:01:00'::timestamp without time zone))
 Planning time: 0.082 ms
 Execution time: 0.053 ms
(4 rows)

For comparison, I've created other index and tried other form:

CREATE INDEX events_ts2_idx ON events (ev_date,ev);
ANALYZE events;

EXPLAIN ANALYZE SELECT pk, ev_date, ev FROM events  WHERE (ev_date,ev) 
    BETWEEN ('2016-02-19','23:50:00') 
        AND ('2016-02-20','0:01:00');
                             QUERY PLAN
--------------------------------------------------------------------------
 Bitmap Heap Scan on events  (cost=189.50..511.36 rows=7143 width=16) (actual time=0.027..0.042 rows=42 loops=1)
   Recheck Cond: ((ROW(ev_date, ev) >=ROW('2016-02-19'::date,'23:50:00'::time without time zone)) AND (ROW(ev_date, ev) <= ROW('2016-02-20'::date, '00:01:00'::time without time zone)))
   Heap Blocks: exact=7
   ->  Bitmap Index Scan on events_ts2_idx  (cost=0.00..187.72rows=7143 width=0) (actual time=0.019..0.019 rows=42 loops=1)
         Index Cond: ((ROW(ev_date, ev) >= ROW('2016-02-19'::date,'23:50:00'::time without time zone))AND(ROW(ev_date, ev) <= ROW('2016-02-20'::date, '00:01:00'::time without time zone)))
 Planning time: 0.079 ms
 Execution time: 0.071 ms
(7 rows)

According to my investigation, my way (using + operator) is better. I recommend to compare with both ways on your machine.

Best Answer

Related Solutions

Postgresql – Do fixed-width rows improve PostgreSQL read performance

Your third query has issues

Convert published_date to an actual date

Postgresql – How to deal with datetime ranges in a table with separate columns for date and time

Related Question

Convert `published_date` to an actual `date`