CODEWITHSUNDEEP
oracle-databaseoracle11g
user2428207
user2428207

Reputation: 825

Change Timestamp granularity

How can I change the granularity of a timestamp in Oracle?

For example: 

28.10.2013 15:15:15

to

28.10.2013 15:00:00

Upvotes: 1

Views: 328

Answers (1)

Andy
Andy

Reputation: 50580

You will use the trunc command

Sample data:

with dates as ( -- generating sample data
select to_date('10/28/2013 09:08:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
select to_date('10/28/2013 09:18:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
select to_date('10/28/2013 09:38:19', 'mm/dd/yyyy hh:mi:ss') dt from dual union all
select to_date('10/28/2013 09:48:19', 'mm/dd/yyyy hh:mi:ss') from dual)

Query to run:

select dt,
    trunc(dt,'hh') as new_dt
from dates

Results in 

DT                   new_dt
-------------------- -------------------
2013-10-28 09:08:19  2013-10-28 09:00:00
2013-10-28 09:18:19  2013-10-28 09:00:00
2013-10-28 09:38:19  2013-10-28 09:00:00
2013-10-28 09:48:19  2013-10-28 09:00:00

Upvotes: 3

Related Questions