Inside the Ocient Engine: Casting, Timestamps, and “Secret” Internal Names

By Jason Arnold, Co-Founder and Distinguished Engineer

If you are an engineer working with Ocient, there is a good chance you have used Postgres. Internally, we strive to support the same syntax as Postgres where reasonable, but we also borrow useful concepts from the DB2 world and add a few ideas of our own.

I wanted to walk through some simple aspects of Ocient SQL syntax—specifically casting and timestamp handling—that will help turn you into a power user. These topics reveal a lot about how our engine thinks.

Let’s start with the basics: getting the current time. In the DB2 world, we called things like current_timestamp “special registers.” In Ocient, we support those, but we also support now() (from Postgres) and standard scalar functions with parentheses.

Here is a query showing the different ways to pull this data. Note that I’m querying sys.dummy3. In Ocient, sys.dummyN is how we generate a sequence. The table exists for all positive integer values and contains a single column c1 that goes from 1 to N inclusive.

The Results:

Pro Tip: Look at the default generated column names in that result set. This is a great way to learn what Ocient is doing internally. You’ll see that now(), current_timestamp, and current_timestamp() all map to the same internal function. Since the names collide, the engine just appends incrementing suffixes (_1, _2) to distinguish them.

Ocient is very flexible with casting. Whether you prefer ANSI SQL, the Postgres short syntax (::), or explicit CAST functions, we likely support it.

However, we also add constructor style casts for every data type. You can always use the data type name as a function to cast to that type. Every data type in Ocient is capable of being cast to a string (CHAR()), and every data type has a way to be created from a string.

Also worth noting: if you don’t have a FROM clause in your query, Ocient injects FROM sys.dummy1 for you.

The Results: 

All Ocient data types have equality and ordering definitions, meaning you can always use them with DISTINCT and ORDER BY.

When we get into adding or subtracting intervals, we diverge a bit from Postgres. We don’t support INTERVAL strings having a mixture of types (e.g., '1 hour 30 minutes'). In fact, INTERVAL isn’t an actual data type in Ocient.

Intervals in Ocient are just int or bigint values that we have temporarily given units to. Because of this, we don’t support CAST(... AS INTERVAL).

The preferred Ocient way to handle date math is—you guessed it—constructors.

The Results:

Because these are just integers under the hood, the return types of subtraction operations are specific:

• Subtracting two timestamps returns a bigint value in nanoseconds.

• Subtracting two dates returns an int value representing days.

  

Extraction: date_part vs. The Ocient Shorthand

Ocient supports the standard EXTRACT and Postgres date_part functions, which return a double. However, we also have our own shorthand versions where the name of the function is just the name of the part you want to extract (e.g., year(), minute()).

These shorthand functions return an int/bigint result.

A good way to remember the difference between the interval constructors and the extractors is the “S”:

• minutes(5) (plural) = 5 minute interval.

• minute(ts_col) (singular) = extract the minute portion.

The Results:

If you check the column headers in the output above, you’ll spot timestamp_to_nanos(). Once again, the generated column names tell you exactly what function Ocient is running internally.

Ocient’s syntax is designed to be familiar to Postgres users while keeping the rigor of enterprise systems like DB2. By using constructor syntax and keeping an eye on your generated column names, you can write cleaner SQL and understand exactly how the engine is processing your data.

Experimenting with sys.dummy and some of these constructors is the fastest way to get comfortable with the Ocient dialect.

Don’t forget you can always find full documentation of Ocient here.