CODEWITHSUNDEEP
timestampdb2seconds
Jobbie Daddy
Jobbie Daddy

Reputation: 71

Ignore seconds portion of TIMESTAMP field in query selection WHERE clause

Sample data:

2018-12-12 10:59:59.892

How do I query the above data and ignore the ":59.892" portion.

Reason: If a user enters 10:59 search parameter, I want records from 10:59:00 - 10:59:59 returned.

Upvotes: 0

Views: 129

Answers (3)

nfgl
nfgl

Reputation: 3202

an other simple way is to use TRUNC_TIMESTAMP like that

where TRUNC_TIMESTAMP(yourtimestamp, 'MI') = timestamp '2018-12-12 10:59:00.000'

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12299

If there is an index on mytab (ts):

with 
  mytab (ts) as
(
  values
    '2018-12-12-10.59.59.892'::timestamp(12)
  , '2018-12-12-11.00.00.000'::timestamp(12)
  , '2018-12-12-11.00.00.000'::timestamp(12) - 0.000000000001 second
)
, userinput (ts) as
(
  values
    '2018-12-12-10.59.00'::timestamp
)
select t.ts
from mytab t
join userinput u on
      t.ts >= u.ts
  and t.ts < u.ts + 1 minute
TS
2018-12-12 10:59:59.892000000000
2018-12-12 10:59:59.999999999999

fiddle

Upvotes: 0

data_henrik
data_henrik

Reputation: 17118

One way would be to use the BETWEEN predicate. It is the equivalent of what you provided in your question: Records between timestamp with 10:59:00 to timestamp with 10:59:59.

You could also check the date first AND then the time of the value. Or you subtract your expected timestamp (2018-12-12 10:59:00) from the timestamps in question and it needs to be less than 1 MINUTE. Many options... Go with BETWEEN for simplicity.

Upvotes: 0

Related Questions