Category: 2024

The algorithm in an algorithm – Builder design pattern

In the following blog post, I would like to explain to you, the design pattern builder, why this is an algorithm in the algorithm and what advantages result from it.

General

The builder is a creational design pattern. It separates the construction of complex objects from their representations, allowing the same construction processes to be reused.

The design pattern consists of a director, the builder interface, and concrete builder implementations. The director is responsible for the abstract construction of the product and has a defined interface with the builder to pass the design instructions. The concrete builders then build the concrete product according to the instructions and can also provide the generated product.

So in the end, the director defines its own little programming language inside the program where the construction instructions can be programmed as algorithm. The builder then executes that algorithm. So we have a program in the program, an algorithm in the algorithm. Crazy!

Example cake recipe

We know such procedures from real life. For example, from the kitchen. When you bake a cake, you take your yellow mixing bowl, the ingredients, and the blue mixer and make the dough. Very concrete.

But now, if someone asks about the recipe, then we abstract it from our concrete equipment to a general manual. There, it only says you are mixing the ingredients, and your yellow mixing bowl and blue mixer are not mentioned. So someone else can bake the cake in their own kitchen with their own equipment. Should your blue mixer ever fail, you can easily carry out the recipe with a whisk or with the new food processor.

Example file generation

An example from programming is the generation of a file. For example, a pdf certificate. If you program everything directly in PDFBox, it works first. But if you ever want to use a different library, or if you also want the certificate as a normal text document or image, you need to rewrite everything.

With the design pattern, you would have an algorithm that says I want “certificate” as a title, then a dividing line, then a table and then this paragraph. Exactly how this will be implemented is not known. The PDFBox builder takes these instructions and creates the file with its own library-specific commands.

If the library or file type changes, only one new builder needs to be written. For example, a text file builder, an image builder or an OpenPDF builder. The logic of how the certificate should look at the end remains unchanged.

Conclusion

Finally, separating the construction from the production offers some advantages. The program is more expandable and modifiable. It also complies with the single responsibility principle. The disadvantage is a close coupling between the product, the concrete builder, and the classes involved in the construction, making it difficult to change the basic process.

Unexpected: Web Sockets close when downloading a file

The browsers work in mysterious ways.

That is, mostly they have certain reasons for behaving why they do, but because so many details are too sophisticated as to have been explicitly defined in some specification (…yet).

Recently, we came across a new problem that was surprisingly non-googleable.

Many of our interactive Web GUIs are based on Web Sockets for real-time updates. As is usual. Now, one live system showed a strange effect, because our Web Socket always broke down when the user clicked on a <a>...</a> link to download a file.

This was strange for multiple reasons, but it boiled down to:

  • It happened under the current Firefox, but not under Chrome
  • It happened not when the link had target = "_blank" (i.e. the link was opening in a new window)
  • The Web Socket closed with the “going away” status code 1001, indicating no further error

And to boil it further down, the solution was to definitely include the download attribute for this particular <a> Element.

The web socket stayed open for both browsers in both of these cases:

<a href="..." target="_blank" rel="noopener noreferrer">
  This leaves the Web Socket intact
</a>

<a href="..." download>
  This also leaves the Web Socket intact
</a>

<a href="...">
  This might close the Web Socket, or not, just as the browser feels today
</a>

(For the rel="noopener noreferrer", see also here.)

The data from the href endpoint was served with the corresponding Content-Disposition and Content-Type HTTP headers. For Chrome, this is enough to identify that link as a download. Firefox was not so sure, it believes that you are actually “going away”.

Efficient integer powers of floating-point numbers in C++

Given a floating-point number x, it is quite easy to square it: x = x * x;, or x *= x;. Similarly, to find its cube, you can use x = x * x * x;.

However, when raising it to the 4’th power, things get more interesting: There’s the naive way: x = x * x * x * x;. And the slightly obscure way x *= x; x *= x; which saves a multiplication.

When raining to the 8’th power, the naive way really loses its appeal: x = x * x * x * x * x * x * x * x; versus x *= x; x *= x; x *= x;, that’s 7 multiplications version just 3. This process can easily be extended for raising a number to any power-of-two N, and will only use O(log(n)) multiplications.

The algorithm can also easily be extended to work with any integer power. This works by decomposing the number into product of power-of-twos. Luckily, that’s exactly what the binary representation so readily available on any computer is. For example, let us try x to the power of 20. That’s 16+4, i.e. 10100 in binary.

x *= x; // x is the original x^2 after this
x *= x; // x is the original x^4 after this
result = x;
x *= x; // x is the original x^8 after this
x *= x; // x is the original x^16 after this
result *= x;

Now let us throw this into some C++ code, with the power being a constant. That way, the optimizer can take out all the loops and generate just the optimal sequence of multiplications when the power is known at compile time.

template <unsigned int y> float nth_power(float x)
{
  auto p = y;
  auto result = ((p & 1) != 0) ? x : 1.f;
  while(p > 0)
  {
    x *= x;
    p = p >> 1;
    if ((p & 1) != 0)
      result *= x;
  }

  return result;
}

Interestingly, the big compilers do a very different job optimizing this. GCC optimizes out the loops with -O2 exactly up to nth_power<15>, but continues to do so with -O3 on higher powers. clang reliably takes out the loops even with just -O2. MSVC doesn’t seem to eliminate the loops at all, nor does it remove the multiplication with 1.f if the lowest bit is not set. Let me know if you find an implementation that MSVC can optimize! All tested on the compiler explorer godbolt.org.

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.

Defeating TimescaleDB or shooting yourself in the foot

Today, almost everything produces periodically data: A car, cloud-connected smart-home solutions, your “smart” refrigerator and yourself using a fitness tracker. This data has to be stored in a certain way to be usable. We want fast access, beautiful charts and different aggregations over time.

There a several options both free and commercial for storing such time-series data like InfluxDB, Prometheus or TimescaleDB. They are optimised for this kind of data taking advantage of different time-series properties:

  • Storing is (mostly) append-only
  • Data points have a (strict) ordering in time
  • Data points often have fixed and variable meta data in addition to the data value itself

In one of our projects we have to store large quantities of data every 200ms. Knowing PostgreSQL as a powerful relational database management system we opted for TimescaleDB that promises time-series features for an SQL database.

Ingestion, probably the biggest problem of traditional (SQL) databases, of the data worked as expected from the beginning. We were able to insert new data at a constant rate without degrading performance.

The problem

However we got severe performance problems when querying data after some time…

So one of the key points of using a time-series database strangely did not work for us… Fortunately, since Timescale is only an extension to PostgreSQL we could use just use explain/explain analyse with our slow queries to find out what was going on.

It directly showed us that *all* chunks of our hypertable were queried instead of only the ones containing the data we were looking for. Checking our setup and hypertable definition showed that Timescale itself was working as expected and chunking the data correctly on the time axis.

After a bit more analysis and thought is was clear: The problem was our query! We used something like start_time <= to AND from <= end_time in our where clause to denote the time interval containing the requested data. Here start_time is the partition column of our hypertable.

This way we were forcing the database to look at all chunks from long ago until our end-time timestamp.

The solution

Ok, so we have to reformulate our where clause that only the relevant chunks are queried. Timescale can easily do this when we do something like start_time >= from AND start_time < to where from and to are the start and end of our desired interval. That way usually only one or only a few chunks of the hypertable are search and everything is lighning-fast even with billions of rows in our hypertable.

Of course the results of our two queries are not 100% the same sematically. But you can easily achieve the desired results by correctly calculation the start and end of the time-series data to fetch.

Conclusion

Time-series databases are powerful tools for todays data requirements. As with other tools you need some understanding of the underlying concepts and sometimes even implementation to not accidently defeat the purpose and features of the tools.

Used in the correct way they enable you to work with large amounts of data in a performant way todays users would expect. We had similar experiences with full text search engines like solr and ElasticSearch, too.

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.

Regular expressions in JavaScript

In one of our applications, users can maintain info button texts themselves. For this purpose, they can insert the desired info button text in a text field when editing. The end user then sees the text as a HTML element.

Now, for better structuring, the customer wants to make lists inside the text field. So there was a need to frame lines beginning with a hyphen with the <li></li> HTML tags.

I used JavaScript to realize this issue. This was my first use of regular expressions in JavaScript, so I had to learn their language-specific specials. In the following article, I explain the general syntax and my solution.

General syntax

For the replacement, you can either specify a string to search for or a regular expression. To indicate that it is a regular expression, the expression is enclosed in slashes.

let searchString = "Test";
let searchRegex = /Test/;

It is also possible to put individual parts of the regular expression in brackets and then use them in the replacement part with $1, $2, etc.

let hello = "Hello Tom";
let simpleBye = hello.replace(/Hello/, "Bye");    
//Bye Tom
let bye = hello.replace(/Hello (.*)/, "Bye $1!"); 
//Bye Tom!

In general, with replace, the first match is replaced. With replaceAll, all occurrences are replaced. But these rules just work for searching strings. With regular expressions, modifiers decide if all matches were searched and replaced. To find and replace all of them, you must add modifiers to the expression.

Modifiers

Modifiers are placed at the end of a regular expression and define how the search is performed. In the following, I present just a few of the modifiers.

The modifier i is used for case-insensitive searching. 

let hello = "hello Tom";
let notFound = hello.replaceAll(/Hello/, "Bye");
//hello Tom
let found= hello.replaceAll(/Hello/i, "Bye");
//Bye Tom

To find all occurrences, independent of whether replace or replaceAll is called, the modifier g must be set.

let hello = "Hello Tom, Hello Anna";
let first = hello.replaceAll(/Hello/, "Bye");
//Bye Tom, Hello Anna
let replaceAll = hello.replaceAll(/Hello/g, "Bye");
//Bye Tom, Bye Anna
let replace = hello.replace(/Hello/g, "Bye");
//Bye Tom, Bye Anna

Another modifier can be used for searching in multi-line texts. Normally, the characters ^ and $ are for the start and end of the text. With the modifier m, the characters also match at the start and end of the line.

let hello = `Hello Tom,
hello Anna,
hello Paul`;
let byeAtBegin = hello.replaceAll(/^Hello/gi, "Bye");     
//Bye Tom, 
//hello Anna,
//hello Paul
let byeAtLineBegin = hello.replaceAll(/^Hello/gim, "Bye");     
//Bye Tom, 
//Bye Anna,
//Bye Paul

Solution

With this toolkit, I can now convert the hyphens into HTML <li></li>. I also remove the line breaks at the end because, in real code, they will be replaced with <br/> in the next step, and I do not want empty lines between the list points.

let infoText = `This is an important field. You can input:
- right: At the right side
- left: At the left side`;
let htmlInfo = infoText.replaceAll(/^-(.*)\n/gm, "<li>$1</li>");
//This is an important field. You can input:
//<li>right: At the right side</li><li>left: At the left side</li>

If you are familiar with the syntax and possibilities of JavaScript, it offers good functions, such as taking over parts of the regular expression.

How to get honest UX Feedback from the Technically Adept User? – Part 1: the problem.

After some consideration, I decided to type this title indeed with the Question Mark at its end, because it is an ongoing process, not a particular conclusion. (… and yes, this is a common theme with everything UX).

We have a wide variety of possible customers and usually an even wider variety of possible users. Sometimes, the mission statement is quite well defined, and sometimes, we just start from the acknowledgement that our customer has some pain point and some trust in that we might be able to help.

Of course, we always carefully evaluate whether we believe our uncertainties to stay manageable or rather reiterate the uncertainties / must-have requirements / minimal viable product with the potential customer, upfront. And if in these cases, we find common ground for a collaboration, it is absolutely crucial to always keep the hand on the steering wheel, always reconsidering what it is the user wants.

From very early on, this affects the aspect of User Experience, which is not something one can apply at the end of the project, some lip gloss, a little treat if everything went well before. Wherever possible, it has to be ingrained in the backbone of the software because sooner or later you could risk driving the project into limbo.

UX Limbo is, when your product is good enough so that a potential user will never openly complain about certain design choices, but still too flawed so they will never actually engage in your software, just because… there’s no flow. No dopamine. They won’t tell you – it’s just difficult.

Enter the “Technically Adept User”. If you have a project in which the end users are actually experts in the domain itselves, this is next-level difficult. Here, the problem is not that the user has a underdeveloped mental model, but that they have a time-tested one which has real value for them. But this might differ from the one that you actually implement; and

  • it might be that you “just” need to listen more accurately
  • it might be that you learned something about the problem that it new to them
  • it might be that there are more than one Technically Adept Users, and they do not notice that they hold incompatible models in their head
  • or they do notice, but due to their different roles they try to one-up each other
  • etc.

So this is the problem. If you play your cards well, you might be able to solve a problem in a way that has never been done before, the customers will feel their burdens taken away, replaced by nothing but overwhelming bliss and (…you get the point) – but it doesn’t take much in perceived imperfection, and the users will start doing mistakes, feel unsatisfied, stupid even, never understanding why you “cannot implement the easiest ideas” (most likely your fault), always pretending, always seeming obsessed, wasting their money, just not “getting” it.

And worse is, you cannot even separate yourself from the product that easily. You can always start a UX Session, like A/B Testing, with the Non-Technically-Adept User, let them tell you their emotions, difficulties, misunderstandings and due to the large gap between their mental model and yours, communication is not that hard. But with too similar mental models, they will always think A if you present B. If they don’t understand why something is implemented as B, they can’t stop their thoughts in rationalizing, maybe even nodding their heads, but what you really would need is the open discussion how a common-ground solution C might look.

They might invite you to a meeting and do not even see the points you are seeing, they might consider their problems solved from minute one – and spend the meeting talking about several possible steps ahead.

The Technically Adept User is a blessing in that their knowledge can take the onus in understing the real problem away from you a bit – but is also a challenge because they need to invest more energy in understanding your differences of understanding. There is no universal solution in how to make them.

I am writing this blog post to keep my own thoughts rolling on that topic. There must be some ways in communicating this gap. It should be done in a way that neither lets the user feel dumb, insulted, their pride taken away; but also in a way that they know that you can be both: knowledgeful about their problem AND flexible in iterating through different solutions, trying what works until something sticks. Time and patience are an issue in this, too.

If you have any suggestions or insights to share, please feel free to do so. I have some ideas in mind but will continue with Part 2. Let’s see what we can learn from this 🙂

My conan 2 Consumer Workflow

A great many things changed in the transition from conan 1.x to conan 2.x. For me, as an application-developer first, the main thing was how I consume packages. The two IDEs I use the most in C++ are Visual Studio and CLion, so I needed a good workflow with those. For Visual Studio, I am using its CMake integration, otherwise known as “folder mode”, which lets you directly open a project with a CMakeLists.txt file in it, instead of generating a solution and opening that. The deciding factor for me is that that uses Ninja as a build tool instead of MSBuild, which often is a lot faster. I have had projects with 3.5x build-time speed ups. As an added bonus, CLion supports very much the same workflow, which reduces friction when switching between platforms.

Visual Studio

First, we’re going to need some local profiles. I typically treat them as ‘build configurations’, with one profile for debug and release on each platform. I put them under version control with the project. A good starting point to create them is conan profile detect, which guesses your environment. To create a profile to a file, go to your project folder and use something like:

conan profile detect --name ./windows_release

Note the ./ in the name, which will instruct conan to create a profile in the current working directory instead of in your user settings. For me, this generates the following profile:

[settings]
arch=x86_64
build_type=Release
compiler=msvc
compiler.cppstd=14
compiler.runtime=dynamic
compiler.version=194
os=Windows

Conan will warn you, that this is only a guess and you should make sure that the values work for you. I usually bump up the compiler.cppstd to at least 20, but the really important change is to change the CMake generator to Ninja, after which the profile should look something like this:

[settings]
arch=x86_64
build_type=Release
compiler=msvc
compiler.cppstd=20
compiler.runtime=dynamic
compiler.version=194
os=Windows

[conf]
tools.cmake.cmaketoolchain:generator=Ninja

Copy and edit the build_type to create a corresponding profile for debug builds.

While conanfile.txt still works for specifying your dependencies, I now recommend directly using conanfile.py from the get go, as some options like overriding dependencies are now exclusive to it. Here’s an example installing the popular logging library spdlog:

from conan import ConanFile
from conan.tools.cmake import cmake_layout


class ProjectRecipe(ConanFile):
    settings = "os", "compiler", "build_type", "arch"
    generators = "CMakeToolchain", "CMakeDeps"

    def requirements(self):
        self.requires("spdlog/1.14.1")

    def layout(self):
        cmake_layout(self)

Note that I am using cmake_layout to setup the folder structure, which will make conan put the files it generates in build/Release for the windows_release profile we created.

Now it is time to install the dependencies using conan install. Make sure you have a clean project before this, e.g. there are no other build/config folders like build/, out/ and .vs/. Specifically, do not open the project in Visual Studio before doing that, as it will create another build setup. You already need the CMakeLists.txt at this point, but it can be empty. For completeness, here’s one that works with the conanfile.py from above:

cmake_minimum_required(VERSION 3.28)
project(ConanExample)

find_package(spdlog CONFIG REQUIRED)

add_executable(conan_example
  main.cpp
)

target_link_libraries(conan_example
  spdlog::spdlog
)

Run this in your project folder:

conan install . -pr:a ./windows_release

This will install the dependencies and even tell you what to put in your CMakeLists.txt to use them. More importantly for the Visual Studio integration, it will create a CMakeUserPresets.json file that will allow Visual Studio to find the prepared build folder once you open the project. If there is no CMakeLists.txt when you call conan install, this file will not be created! Note that you generally do not want this file under version control.

Now that this is setup, you can finally open the project in Visual Studio. You should see a configuration named “conan-release” already available and CMake should run without errors. After this point, you can let conan add new configurations and Visual Studio should automatically pick them up through the CMake user presets.

CLion

The process is essentially the same for CLion, except that the profile will probably look different, depending on the platform. Switching the generator to Ninja is not as essential, but I still like to do it for the speed advantages.

Again, make sure you let conan setup the initial build folders and CMakeUserPresets.json and not the IDE. CLion will then pick them up and work with them like Visual Studio does.

Additional thoughts

I like to create additional script files that I use to setup/update the dependencies. For example, in windows, I create a conan_install.bat file like this:

@echo Installing debug dependencies
conan install . -pr:a conan/windows_debug --build=missing %*
@if %errorlevel% neq 0 exit /b %errorlevel%

@echo Installing release dependencies
conan install . -pr:a conan/windows_release --build=missing %*
@if %errorlevel% neq 0 exit /b %errorlevel%

Have you used other workflows successfully in these or different environments? Let me know about them!

Time Intervals in SQL

SQL not only supports date and timestamp data types but also time intervals.

An interval in SQL represents a period of time, such as days, hours, minutes, or seconds. SQL supports intervals in two main types: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

The two types

YEAR TO MONTH is used to represent a span of years and months. Examples include calculating ages, durations of contracts, or project timelines.

DAY TO SECOND is used to represent a span of days, hours, minutes, and seconds. Examples include scheduling events, logging activities, and tracking precise durations.

-- Represents 1 year and 6 months
INTERVAL '1-6' YEAR TO MONTH

-- Represents 5 days, 12 hours, 30 minutes, and 45 seconds
INTERVAL '5 12:30:45' DAY TO SECOND

This distinction may seem arbitrary at first, but it aligns with real-world use cases. An interval of “2 months” is clearly different from “60 days,” even though they might seem similar. The former is handled as a “YEAR TO MONTH” interval, and the latter as a “DAY TO SECOND” interval, making it clear how operations should be performed. Storing only the relevant units also reduces the amount of space required. For instance, storing a “YEAR TO MONTH” interval doesn’t need to allocate space for hours or seconds, thus saving storage.

Despite its name a “DAY TO SECOND” interval can also include fractional seconds:

INTERVAL '1 12:30:45.123' DAY TO SECOND

There are also shorter forms of interval literals if you only want to use one or several units of the interval:

For “YEAR TO MONTH”:

INTERVAL '3' YEAR   -- 3 years
INTERVAL '5' MONTH  -- 5 months

For “DAY TO SECOND”:

INTERVAL '2' DAY                -- 2 days
INTERVAL '5:30' HOUR TO MINUTE  -- 5 hours and 30 minutes
INTERVAL '15' MINUTE            -- 15 minutes
INTERVAL '30' SECOND            -- 30 seconds
INTERVAL '45.678' SECOND        -- 45.678 seconds

Note that the syntax always uses the singular forms of the unit words.

The two types can be used as data types for table columns:

CREATE TABLE interval_examples (
  id NUMBER,
  a INTERVAL YEAR TO MONTH,
  b INTERVAL DAY TO SECOND
);

Calculations with intervals

Intervals can be added to or subtracted from dates to calculate future or past dates.

-- Adding 10 days to the current date
CURRENT_DATE + INTERVAL '10' DAY

-- Subtracting 3 months from a specific date
DATE '2024-06-14' - INTERVAL '3' MONTH

It’s important to understand that intervals of type YEAR TO MONTH do not have a fixed duration. The number of days added to CURRENT_TIMESTAMP when you add an INTERVAL ‘1’ MONTH depends on the current month and year, as different months have different numbers of days.

Intervals can also be compared to each other with the usual operators such as =, <, <=, >, >=, <>.

Combining both types

Suppose you need to add an interval of 1 year, 2 months, 3 days, 4 hours, 5 minutes, and 6 seconds to the current timestamp. In this case you need to combine intervals of the two types:

SELECT (CURRENT_TIMESTAMP + INTERVAL '1-2' YEAR TO MONTH) + INTERVAL '3 04:05:06' DAY TO SECOND AS result;

Notes on PostgreSQL

PostgreSQL supports the above standard interval syntax, but it also supports a unified syntax for intervals with the following syntax:

INTERVAL '1 year 2 months'
INTERVAL '10 days 12 hours 30 minutes 45.123 seconds'
INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'

In fact, in PostgreSQL, YEAR TO MONTH and DAY TO SECOND are not a distinct data type on their own; they are specializations of the broader INTERVAL data type.

It also supports multiplication and division of intervals:

-- Results in an interval of 6 days
SELECT INTERVAL '2 days' * 3 AS multiplied_interval;

-- Results in an interval of 2 hours
SELECT INTERVAL '3 hours' / 1.5 AS divided_interval;