Oracle - select date / time in milliseconds from the DATE datatype field

Question

I have a last_update_date column defined as a DATE field

I want to get time in milliseconds.

I currently have:

 TO_CHAR(last_update_date,'YYYY-DD-MM hh:mi:ss am') 

But I also want to get milliseconds.

I did a bit of work on Google and I think that DATE fields will not have milliseconds. only TIMESTAMP fields.

Is there a way to get milliseconds? I have no way to change the data type for the field.

Answer 1

Oracle's DATE fields only store data for up to a second, so there is no way to provide anything more accurate. If you need higher precision, you should use a different type, like TIMESTAMP.

Here 's a link to another SO question regarding Oracle date and time accuracy.

Answer 2

As RC says, the DATE type only supports the granularity to second.

If converting to TIMESTAMP is really not an option, then what about adding another numeric column that just contains milliseconds?

This parameter will be more cumbersome than the TIMESTAMP column, but it can be workable if type conversion is not possible.

Answer 3

In a similar situation, when I could not change the fields in the table (I could not afford to “break” third-party software), but needed subgrid accuracy, I added an additional 1: 1 table and a trigger after inserting it into the original table to place the timestamp in an additional table.

If you need to know the ORDER records added within one second, you can do the same only by using the sequence as the data source for the extra field.