CODEWITHSUNDEEP
sqlsql-server
RH1212354
RH1212354

Reputation: 17

timestamp to datetime using dateadd is slow query

I have a table with timestamp column ts - linux timestamp (bigint), I want to convert it to datetime and I am using the following query:

Example:

select * from table1
where dateadd(S, ts, '1970-01-01 00:00:00')>'2019-09-01'
and  dateadd(S, ts, '1970-01-01 00:00:00')<'2019-09-10'

But dateadd is making the query very slow, is there other way to convert timestamp into datetime or a way to make the current query run faster?

Upvotes: 0

Views: 88

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37500

I am afraid there's nothing you can do. You use column ts in your statement, which is wrapped in a function, which would diable possible indexes on that column.

But you could rewrite this query, so the coulmn is not wrapped in any function:

where ts > datediff(s, '1970-01-01', '2019-09-01')
and ts < datediff(s, '1970-01-01', '2019-09-10')

Upvotes: 1

Related Questions