CODEWITHSUNDEEP
sqlpostgresqldate-arithmetic
Henrik Poulsen
Henrik Poulsen

Reputation: 93

Compare date and seconds to timestamp

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.

Upvotes: 1

Views: 869

Answers (3)

user330315
user330315

Reputation:

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)

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656251

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:

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:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just add the two together:

created_date + created_time * interval '1 second' > ?

Upvotes: 2

Related Questions