Compare date and seconds to timestamp

Question

I have a Postgres table that stores created timestamp in a created_date DATE column and a created_time INT column holding seconds since midnight.

EDIT: The table is on a customer's production db and stores data from an older system, its not possible to make schema changes.

I would like to make a selection based on created timestamp.

In MySQL I would write:

SELECT * FROM MyCustomers
WHERE ADDTIME(created_date,SEC_TO_TIME(created_time)) > 'sometimestampliteral'

How would that look in PostgreSQL?

I can see examples in the manual, but they are all using literal values and not values from table columns.

I am curious as to the rationale for not having a timestamp column that just stores both these values. — Thom Brown, May 27 '20 at 20:28
I agree with Thom: it would be much better to store this in a single `timestamp` column — , May 27 '20 at 20:33
`'somedateliteral'` is meant to be a `'timestamp_literal'`, I suppose? — Erwin Brandstetter, May 27 '20 at 23:21

score 2 · Accepted Answer · answered May 27 '20 at 19:52

2

Just add the two together:

created_date + created_time * interval '1 second' > ?

answered May 27 '20 at 19:52

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 2 · 2020-05-28T05:41:26.777

2

You can use make_interval() to turn the seconds into an interval which in turn can be added to a date column to construct a proper timestamp:

created_date + make_interval(secs => created_time)

edited May 28 '20 at 05:41

answered May 27 '20 at 20:33

Erwin Brandstetter · Answer 3 · 2020-05-27T23:19:49.650

While stuck with your design

Assuming ISO format for 'somedateliteral'.
Since it's supposed to be a timestamp literal, I'll name it 'timestamp_literal' instead.

SELECT *, created_date + make_interval(secs => created_time) AS created_at
FROM   mycustomers
WHERE  (created_date, created_time)
     > (date 'timestamp_literal', EXTRACT(epoch FROM time 'timestamp_literal')::int);

date 'timestamp_literal' and time 'timestamp_literal' take the respective date / time part from the literal. (The latter breaks if there is no time part, while the former works with a date literal as well, obviously.)

Why?

To make it "sargable". See:

What does the word “SARGable” really mean?

Contrary to solutions computing a timestamp on the fly ¹, this query can use a basic multicolumn index:

CREATE INDEX multicolumn_created ON mycustomers (created_date, created_time);

(¹ You could create a matching expression index to make it work ...)

Postgres can make perfect use of this index with ROW value comparison. (Last time I looked, MySQL couldn't do this.) See:

Proper fix

Storing timestamps as date + integer typically is a design error without benefits. Use timestamp instead. You may want to switch to the generally preferred timestamptz while being at it. See:

Ignoring time zones altogether in Rails and PostgreSQL

Other answers already provided expressions to convert (date, integer) to timestamp - which you can use to fix your table:

BEGIN;

-- drop all depending objects: indexes, views, ...

ALTER TABLE mycustomers
   ALTER created_date TYPE timestamp
      USING (created_date + make_interval(secs => created_time)) 
 , DROP COLUMN created_time;

ALTER TABLE mycustomers RENAME created_date TO created_at;  -- or whatever

-- recreate all depending objects: indexes, views, ...

COMMIT;

Adopt all queries using those columns, obviously. The query at hand simply becomes:

SELECT * FROM mycustomers WHERE created_at > 'somedateliteral';

db<>fiddle here

Compare date and seconds to timestamp

3 Answers3

While stuck with your design

Why?

Proper fix