Thanks for the question, Geraldo.
Asked: April 23, 2020 - 12:25 pm UTC
Last updated: May 04, 2020 - 3:35 am UTC
Version: 18.6
Viewed 1000+ 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 Connor 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.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment