Tag: date

Oracle DB: How to Pick the Right Function for Current Date and Time


When working with date and time in Oracle, you have several functions available to get the current date and time. Three important ones are CURRENT_DATE, CURRENT_TIMESTAMP, and SYSDATE. Let’s see how they are different and when you should use each one.

The CURRENT_DATE function gives you the current date and time based on the time zone of the session you are in. It returns this information in a simple DATE format, which includes the date and time up to the second but does not show fractions of a second or the time zone. For example, if you run:

SELECT CURRENT_DATE FROM dual;

You might get a result like 29-JUL-24 03:43:19 PM. This shows the current date and time according to your session’s time zone.

You can set the session’s time zone to a specific offset from UTC. For example, to set the time zone to UTC+5:30:

ALTER SESSION SET TIME_ZONE = '+05:30';

Use CURRENT_DATE when you need the date and time for tasks that are specific to a certain time zone. It’s good for simple reports or calculations that don’t need to worry about fractions of a second or different time zones.

The CURRENT_TIMESTAMP function provides more detail. It gives you the current date and time, including fractions of a second and the time zone. This function returns the value in the TIMESTAMP WITH TIME ZONE format. For example, if you run:

SELECT CURRENT_TIMESTAMP FROM dual;

You might see something like 29-JUL-24 15.43.19.123456 PM +01:00. This includes the date, time, fractions of a second, and the time zone offset.

Use CURRENT_TIMESTAMP when you need precise time details, such as for logging events, tracking changes, or working across different time zones. It’s useful when you need to know the exact time down to the fraction of a second and the time zone.

The SYSDATE function gives you the current date and time from the database server’s clock. It’s similar to CURRENT_DATE in that it returns the date and time up to the second but not fractions of a second or time zone information. For example, if you run:

SELECT SYSDATE FROM dual;

You might get 29-JUL-24 03:43:19 PM. This shows the current date and time based on the server’s clock.

Use SYSDATE when you need the current date and time according to the server, not the session. This is helpful for server-side operations, scheduling tasks, and ensuring consistency across database operations that rely on the server’s time.

With this information, you should now be able to choose the right function for your use case.

A neat trick for finding dates

One of my fundamental driving forces for designing a suitable user experience is the motto of “no scrolling”. It is not a hard principle in the sense of Bret Victor’s “Inventing on Principle”, but it is motivation enough to always look for context-aware ordering of lists and tables.

One of the cornerstones of such “scrolling-free” or “scrolling-reduced” applications is a possibility for the user to define a context of work. You might call it an ordinary search field, but it is highly interactive (immediate feedback, as mandated by Bret Victor) and doesn’t lead to a special result view. It just reduces the amount of data that is presented to the user. The idea behind the context field is that the user has some idea about the piece of work she wants to edit. So, if for example she remembers that the customer of an order was named “Miller”, she would type “mil” in the context field and have the list of orders reduced to just the “Millers” and “Camilles” (the latter contains “mil” in the middle).

This works fine for text-based information, but less so for numerical data. Most users typically remember text like names better than numbers like values or phone numbers, so it fits the natural inclination of humans. And then there is a form of data that is remembered easily and used for orientation, but presented as numbers: dates.

If you store a date in a persistent storage, it is probably stored as a number, several numbers or a short piece of text like “2024-12-24”. To make a date searchable, the textual representation of “2024-12-24” is a good start, but we can do better with a simple trick:

Instead of just using one textual representation for the search index (or whatever search functionality you use), you can append several representations of the same date at once:

“2024-12-24 Tuesday, December 24, 2024”

Here, we’ve used a code like this:

String searchtext = xmas.toString() + " " + xmas.format(DateTimeFormatter.ofLocalizedDate(FormatStyle.FULL));

This enables your user to restrict to a context of “dec” for “December” or even “tue” for “Tuesday”.

This search-extended representation of the date is never shown on screen, so it doesn’t have to be readable or brief. It should contain the concepts of managing dates that your users will facilitate during their normal interaction with the data (not with your application!). So it might even be useful to add several more representations like a relative period (“in 5 months, 16 days”) and maybe special date names (“christmas, xmas”). It might be useful to cut down on filler words like “months” or “days”, because they are included in virtually every date you’ll search. So the relative period might come down to “5 five, 16 sixteen”. If your search allows for multiple texts that all need to be present (which I would encourage because in my experience, that’s how people triangulate: “It was Miller and at the end of the year”), you might add the filler words again, because it allows for a context string like “5 mo” with matches with “in 5 months” (and “5 months ago”, but that’s another topic).

In a nutshell, my trick is to craft the textual representation of data for the search (not the visualisation!) in accordance to the navigation patterns of my users. If they can rely on the data being focussed effectively, they won’t miss the scrollbars.