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: 448526024.689048 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: 1710830024694 milliseconds

Timezones

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 https://asktom.oracle.com/ords/asktom.search?tag=to-date-with-no-month 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:
to_date('2015-feb')
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.

D_YYYYD_YYYY_MMD_YYYY_MM_DDD_YYYY_DDD_MM_DD
2024-03-012024-01-012024-01-012024-03-152024-01-15

Code

Region

Identification
Sequence
Title
Type
Source
Source Type
Table Name

View

View
Name
DDL