Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: April 23, 2020 - 12:25 pm UTC

Answered by: Connor McDonald - Last updated: May 04, 2020 - 3:35 am UTC

Category: Database Development - Version: 18.6

Viewed 100+ times

You Asked

Hello, Ask TOM Team.

I have three DATE columns in some tables. I have been asked to do some math and show it in milliseconds. I need to change the DATE datatype to TIMESTAMP, maybe TIMESTAMP(3).

This is my query:

SELECT COUNT(*) QTY_DOCS,
ROUND(MIN((register_date-received_date)*24*60*60*1000),2) MIN_MS_REG,
ROUND(AVG((register_date-received_date)*24*60*60*1000),2) AVG_MS_REG,
ROUND(MAX((register_date-received_date)*24*60*60*1000),2) MAX_MS_REG,
ROUND(MIN((updated_date-register_date)*24*60*60*1000),2) MIN_MS_UPDATE_STATE,
ROUND(AVG((updated_date-register_date)*24*60*60*1000),2) AVG_MS_UPDATE_STATE,
ROUND(MAX((updated_date-register_date)*24*60*60*1000),2) MAX_MS_UPDATE_STATE
FROM user.table t
WHERE t.register_date >= TO_DATE (:FIRSTDATE,'DD/MM/RRRR')
AND t.register_date < TO_DATE (:SECONDDATE,'DD/MM/RRRR') + 1

1. How the change from DATE to TIMESTAMP data type will impact performance (index on register_date,...). Is the index on TIMESTAMP different or just normal index and I can keep using TO_DATE function?

2. What other things do I have to keep in mind about all this?

and we said...

Some things to note:

Performance will not be impacted. You can index TIMESTAMP columns just like DATE columns.

TIMESTAMPS will use a little more space, but its unlikely you'll notice unless you're into the billions of rows.

Try not to do any implicit date conversions. So make sure your predicates are:

WHERE t.register_date >= TO_TIMESTAMP (:FIRSTDATE,'DD/MM/RRRR')


Subtracting two timestamps does not give a number, it gives an interval, so you need to adjust your code, eg

SQL> select systimestamp - (systimestamp-.123) from dual;

SYSTIMESTAMP-(SYSTIMESTAMP-.123)
----------------------------------------------------------
+000000000 02:57:07.392000


To get the bits and pieces out, you use EXTRACT, eg

SQL> with t as (
  2    select systimestamp - (systimestamp-.123) x from dual
  3  )
  4  select
  5    extract(day from x),
  6    extract(hour from x),
  7    extract(minute from x),
  8    extract(second from x)
  9  from t;

EXTRACT(DAYFROMX) EXTRACT(HOURFROMX) EXTRACT(MINUTEFROMX) EXTRACT(SECONDFROMX)
----------------- ------------------ -------------------- --------------------
                0                  2                   57                7.304

1 row selected.




and you rated our response

  (3 ratings)

Reviews

Review

April 24, 2020 - 3:44 am UTC

Reviewer: Geraldo

Thanks for the answer, Connor.

It really helped!
Connor McDonald

Followup  

April 24, 2020 - 5:19 am UTC

glad we could help

Follow Up

May 01, 2020 - 9:48 pm UTC

Reviewer: A reader

Can I use TO_DATE function to respond requirements that do not need the millisecond component? Or in TIMESTAMP columns I have to use TO_TIMESTAMP function? Because I need the milliseconds just to express some value with that component but all my queries use filters like WHERE t.register_date >= TO_DATE (:FIRSTDATE,'DD/MM/RRRR')
AND t.register_date < TO_DATE (:SECONDDATE,'DD/MM/RRRR') + 1, where milliseconds are not needed.

Thanks in advanced.
Connor McDonald

Followup  

May 04, 2020 - 3:35 am UTC

If "register_date" is a timestamp, then compare it to a timestamp. You can have a timestamp with no milliseconds - thats fine. You would STILL use

WHERE t.register_date >= TO_TIMESTAMP (:FIRSTDATE,'DD/MM/RRRR')


not

WHERE t.register_date >= TO_DATE(:FIRSTDATE,'DD/MM/RRRR')

Review

May 04, 2020 - 3:50 am UTC

Reviewer: Geraldo

Thanks for the answer.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.