oracle PLSQL- Only provide hour, minute and second from timestamp

Question

Is there a way to only extract HOUR,MINUTES and Seconds from timestamp.. Along with date ofc but i dont want it to show fractional_seconds_precision with it.. So for example when I type the following:

TO_TIMESTAMP('2017-12-02 05:00:00', 'YYYY-MM-DD HH24:MI:SS')

I only want what i specified in the format to be shown, I dont want FF to be shown but whenever I check the output, FF always comes along with it. How do I remove it? Also, I wanted to ask, Is there a way to compare the dates typed using timestamp to be compared with dates typed using TO_DATE? what if I want one to be timestamp and other to be in a TO_DATE format? Thanks

Answer 1

Your question doesn't really make sense, but I understand why you're asking it.

Essentially, you're saying "I have this string "1" but when I parse it as a number it has loads of numbers after the decimal: 1.0000000000000. How can I store it without those extra zeroes"

And the answer is, you can't: 1 and 1.000000000 are the same thing, they're stored the same way, and it's not up to you to decide how it's stored. What you're seeing as an output of 1.000000000 is because you haven't asked for the internal numeric 1 to be output in a particular format (like $1.00), so you're just getting it in the default format for either the database or the query tool you're using

Ultimately, don't worry about it - hiding or showing those zeroes as a developer isn't going to matter a jot. Including them or not when you're querying your timestamp again isn't going to matter. When your front end app is dumping stuff to a report, it should retrieve the timestamp as is from Oracle and do the formatting itself; store $1 as a number 1, not a string "$1". When it comes time to put it on a report, format it as $1.00 in the code that makes the report.

If you're running queries that use the timestamp fields, you can compare with dates just fine... Just remember that dates and timestamps have a time component too, so query them using a range. For example to get all rows whose timestamp is today:

select * from t where mytimestamp >= trunc(sysdate) and mytimestamp < trunc(sysdate)+1

This is better than doing:

select * from t where trunc(mytimestamp) = trunc(sysdate)

Because trunc(mystimestamp) means you're manipulating table data to run your query. Unless you have specifically indexed the output of trunc(mytimestamp) then this query could run like a dog, as an index on mytimestamp column cannot be used if the query calls for table data to be manipulated before it is compared

It may help you to consider that internally, dates are typically stored as the number of days since a certain point in history. Times are fractions of a day. Internally 01-jan-1970 00:00 might be 0, and 01-jan-1970 18:00 i.e. 6pm, is three quarters the way through the day, so it'll be stored as 0.75. 02-jan-1970 would hence be stored as 1. When you use trunc you chop off the decimal part. This is why trunc "removes" the time froma date. It doesn't "remove" it - you can't remove a time froma date, because everything that happens, happens at some point in time.. But it does reduce the time component to midnight, so trunc()ing all the adtes in a table effectively makes them all happen at midnight on the day in question.

Oracle can trunc to other things when it comes to dates, like truncing to the start of the month, but for now, remember that internally theyre fractional numbers, query them using ranges (so "between 01 jan 1970 and 02 jan 1970" is really saying "between 0 and 1" - which is what 0.75 i.e 6pm on 01 jan 1970, is)

Answer 2

When you convert a string to a timestamp (or date) the result is an internal Oracle format which you don't generally need to worry about. You just need to know that internally it doesn't look anything like what you entered.

When you query a timestamp your client decides how to format the timestamp for display using your NLS settings. While you can modify those it's generally better to not rely on them - as someone else running your code may have different settings - and explicitly convert the timestamp to a string using the to_char() function and a suitable format model. In this case:

select to_char(your_column, 'YYYY-MM-DD HH24:MI:SS') from your_table

But only do that for display purposes; while you're working with timestamp values - adding or removing intervals, for instance - leave it as a timestamp, and avoid doing anything that will cause implicit conversion to a different data type, as that can cause all sort of issues.

In your example the fractional seconds part will be zero, but that may not always be the case. If you actually have values with non-zero fractional seconds, and want to ignore those while doing calculations, you can cast() the value to a slightly different data type, such as a timestamp with restricted precision:

select cast(to_timestamp('2017-12-02 05:00:00.123', 'YYYY-MM-DD HH24:MI:SS.FF3') as timestamp(0))
from dual;

CAST(TO_TIMESTAMP('2017-12-0
----------------------------
02-DEC-17 05.00.00.000000000

(displayed by my client using my NLS settings!), or you can cast to a date instead depending on what kind of calculations you're doing - you may implicitly be doing that already. An Oracle date has the same precision as a timestamp(0) as both can hold values defined down to the second, but they are stored differently.

If you compare a date with a timestamp then Oracle will implicitly convert them, which can have consequences for its ability to utilise indexes.