Skip to Main Content

Average between Dates

To my surprise, doing something like this is not possible in (PL)SQL:
This is a relatively elegant workaround:

Timestamp to Date

Don't use:
But use:
In "normal" queries the first method works, but it won't work correctly in views.

For "non-truncated" days, use:

Difference between Timestamps

Of course you can use something like this: But that gives you an interval, which I almost never need.

To get the (more convenient) difference between timestamps in seconds:
Result: 458546397.085526 seconds

Unix (Epoch) time

Unix (Epoch) time is the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, minus leap seconds.

To calculate the number of milliseconds elapsed since then, taking your timezone into account, use this query:
Result: 1720850397090 milliseconds


Conversion of a date from a timezone to the "database timezone":
and vice versa:

To_date YYYY

If you use something like to_date('2024',YYYY'), you have only a 1 in 12 chance of getting the result I expected (1st Jan 2024).
Any other month will mess it up - only in January will the result be as I expected, in e.g. August you will get 1st Aug 2024.

The comment by Connor on states:

I havent seen a documentation reference that explicitly states this rule (but its been this way for as long as I've used oracle).
But it makes sense (to me :-)) to have the rules like this because you'll always get back a valid date.
If you used "current day" not "first of month" then something like:
would work on January 25th but fail if you ran it on January 31st.

I don't think I agree with that one...

Note the YYYY-DD example (also given in the post above): select to_date('2015-11', 'YYYY-DD') from dual; --> 11-sep-2015 - I rest my case :-)
The final MM-DD example returns a result in the current year, which sort of makes sense. Although an exception or using 1970 would be just as (il)logical I suppose.


Timestamp to_char

Using to_char to convert a timestamp into a string can produce different results in SQL and PL/SQL.

in SQL this returns 20240704062506.298731:
in PL/SQL this returns 20240704062506.298731000:
The documentation about the FF format model elements states: "If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision."
Apparently this is not the same for SQL and PL/SQL - not by default anyway, your database may be set up differently.
If you do specify a digit, e.g. FF6 instead of FF, it works as expected.

I noticed this when I got a "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" exception because of this.
As you can imagine, this took a while to pinpoint and debug...



Source Type
Table Name