Don’t Format Dates in SQL. Use the DATE Literal!

I’m seeing people do this all the time. They want to hammer a date or timestamp constant into their SQL query, and the only function they know is the TO_DATE() or TO_TIMESTAMP() date parsing function:

SELECT TO_DATE ('20150801', 'yyyymmdd')
FROM DUAL;

As observed in this Stack Overflow question, for instance:

TO_DATE ('20150801', 'yyyymmdd') AS DAY_20150801_TOTAL,
TO_DATE ('20150802', 'yyyymmdd') AS DAY_20150802_TOTAL,
TO_DATE ('20150803', 'yyyymmdd') AS DAY_20150803_TOTAL,
TO_DATE ('20150804', 'yyyymmdd') AS DAY_20150804_TOTAL,
TO_DATE ('20150805', 'yyyymmdd') AS DAY_20150805_TOTAL,
TO_DATE ('20150806', 'yyyymmdd') AS DAY_20150806_TOTAL,
TO_DATE ('20150807', 'yyyymmdd') AS DAY_20150807_TOTAL,
TO_DATE ('20150808', 'yyyymmdd') AS DAY_20150808_TOTAL,
TO_DATE ('20150809', 'yyyymmdd') AS DAY_20150809_TOTAL,
TO_DATE ('20150810', 'yyyymmdd') AS DAY_20150810_TOTAL,
TO_DATE ('20150811', 'yyyymmdd') AS DAY_20150811_TOTAL, [...]

Date parsing is important only if your date input is really stored as strings in a table and in a custom format, and you want to manipulate the dates. If you are using date constants, please save some time and improve readability by using the DATE literal or TIMESTAMP literal.

Here’s an example:

SELECT 
  DATE '2015-08-01' AS d,
  TIMESTAMP '2015-08-01 15:30:00' AS ts
FROM DUAL;

Most major RDBMS support the above SQL standard literals. The date format that you should use with these is the ISO 8601 format. Advantages of these literals:

  • They’re really constants and as such, they’re parsed only by the SQL parser, not the execution engine (e.g. when put in a WHERE clause)
  • They’re much more readable than any other option

It’s the little things that sum up to make the big difference.

What’s Even Harder Than Dates and Timezones? Dates and Timezones in SQL / JDBC!

(Notice, this post has been written a while ago. jOOQ now supports JSR 310 data types)

There was an interesting discussion recently on the jOOQ mailing list about jOOQ’s current lack of out-of-the-box support for TIMESTAMP WITH TIME ZONE data types.

No one said that date, time and timezones are easy! There’s an amusing piece here, which I recommend reading:
Falsehoods programmers believe about time

And when that’s not enough, read also:
More falsehoods programmers believe about time

I personally like the bit about programmers erroneously believing that “Unix time is the number of seconds since Jan 1st 1970.”… unix time doesn’t have a way to represent leap seconds ;)

Back to JDBC

Here’s an interesting Stack Overflow answer by Mark Rotteveel, the Jaybird developer (Firebird JDBC driver):
Is java.sql.Timestamp timezone specific?

Mark’s explanation can be observed as follows (I’m using PostgreSQL here):

Connection c = getConnection();
Calendar utc = Calendar.getInstance(
    TimeZone.getTimeZone("UTC"));

try (PreparedStatement ps = c.prepareStatement(
    "select"
  + "  ?::timestamp,"
  + "  ?::timestamp,"
  + "  ?::timestamp with time zone,"
  + "  ?::timestamp with time zone"
)) {

    ps.setTimestamp(1, new Timestamp(0));
    ps.setTimestamp(2, new Timestamp(0), utc);
    ps.setTimestamp(3, new Timestamp(0));
    ps.setTimestamp(4, new Timestamp(0), utc);

    try (ResultSet rs = ps.executeQuery()) {
        rs.next();

        System.out.println(rs.getTimestamp(1) 
                 + " / " + rs.getTimestamp(1).getTime());
        System.out.println(rs.getTimestamp(2, utc)
                 + " / " + rs.getTimestamp(2, utc).getTime());
        System.out.println(rs.getTimestamp(3) 
                 + " / " + rs.getTimestamp(3).getTime());
        System.out.println(rs.getTimestamp(4, utc)
                 + " / " + rs.getTimestamp(4, utc).getTime());
    }
}

The above program uses all permutations of using timezones and not using timezones in Java and in the DB, and the output is always the same:

1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0

As you can see, in each case, the UTC timestamp 0 was correctly stored and retrieved from the database. My own locale is Switzerland, thus CET / CEST, which was UTC+1 at Epoch, which is what is getting output on Timestamp.toString().

Things get interesting when you use timestamp literals, both in SQL and/or in Java. If you replace the bind variables as such:

Timestamp almostEpoch = Timestamp.valueOf("1970-01-01 00:00:00");

ps.setTimestamp(1, almostEpoch);
ps.setTimestamp(2, almostEpoch, utc);
ps.setTimestamp(3, almostEpoch);
ps.setTimestamp(4, almostEpoch, utc);

This is what I’m getting on my machine, again in CET / CEST

1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000
1970-01-01 00:00:00.0 / -3600000

I.e. not Epoch, but the timestamp literal that I sent to the server in the first place. Observe that the four combinations of binding / fetching still always produce the same timestamp.

Let’s see what happens if the session writing to the database uses a different timezone (let’s assume you’re in PST) than the session fetching from the database (I’m using again CET or UTC). I’m running this program:

Calendar utc = Calendar.getInstance(
    TimeZone.getTimeZone("UTC"));

Calendar pst = Calendar.getInstance(
    TimeZone.getTimeZone("PST"));

try (PreparedStatement ps = c.prepareStatement(
    "select"
  + "  ?::timestamp,"
  + "  ?::timestamp,"
  + "  ?::timestamp with time zone,"
  + "  ?::timestamp with time zone"
)) {

    ps.setTimestamp(1, new Timestamp(0), pst);
    ps.setTimestamp(2, new Timestamp(0), pst);
    ps.setTimestamp(3, new Timestamp(0), pst);
    ps.setTimestamp(4, new Timestamp(0), pst);

    try (ResultSet rs = ps.executeQuery()) {
        rs.next();

        System.out.println(rs.getTimestamp(1)
                 + " / " + rs.getTimestamp(1).getTime());
        System.out.println(rs.getTimestamp(2, utc)
                 + " / " + rs.getTimestamp(2, utc).getTime());
        System.out.println(rs.getTimestamp(3)
                 + " / " + rs.getTimestamp(3).getTime());
        System.out.println(rs.getTimestamp(4, utc)
                 + " / " + rs.getTimestamp(4, utc).getTime());
    }
}

It yields this output:

1969-12-31 16:00:00.0 / -32400000
1969-12-31 17:00:00.0 / -28800000
1970-01-01 01:00:00.0 / 0
1970-01-01 01:00:00.0 / 0

The first timestamp was Epoch stored as PST (16:00), then the timezone information was removed by the database, which turned Epoch into the local time you had at Epoch (-28800 seconds / -8h), and that’s the information that is really stored.

Now, when I’m fetching this time from my own timezone CET, I will still want to get the local time (16:00). But in my timezone, this is no longer -28800 seconds, but -32400 seconds (-9h). Quirky enough?

Things go the other way round when I’m fetching the stored local time (16:00), but I’m forcing the fetch to happen in UTC, which will produce the timestamp that you’ve stored, originally in PST (-28800 seconds). But when printing this timestamp (-28800 seconds) in my timezone CET, this will now be 17:00.

When we use the TIMESTAMP WITH TIME ZONE data type in the database, the timezone is maintained (PST), and when I fetch the Timestamp value, no matter if using CET or UTC, I will still get Epoch, which was safely stored to the database, printed out as 01:00 in CET.

Whew.

TL;DR:

(Notice, this post has been written a while ago. jOOQ now supports JSR 310 data types)

When using jOOQ’s, if the correct UTC timestamp matters to you, use TIMESTAMP WITH TIMEZONE, but you’ll have to implement your own data type Binding, because jOOQ currently doesn’t support that data type. Once you use your own data type Binding, you can also use Java 8’s time API, which better represents these different types than java.sql.Timestamp + the ugly Calendar.

If the local time matters to you, or if you’re not operating across time zones, you’re fine using TIMESTAMP and jOOQ’s Field<Timestamp>.

Lucky you, if you’re like me, operating in a very small country with a single time zone where most local software just doesn’t run into this issue.

Are You Binding Your Oracle DATEs Correctly? I Bet You Aren’t

Oracle database has its ways. In my SQL talks at conferences, I love to confuse people with the following Oracle facts:

sql-trivia-1

… and the answer is, of course:

sql-trivia-2

Isn’t it horrible to make empty string the same thing as NULL? Please, Oracle…

The only actually reasonable slide to follow the previous two is this one:

sql-trivia-3

But the DATE type is much more subtle

So you think VARCHAR2 is weird?

Well, we all know that Oracle’s DATE is not really a date as in the SQL standard, or as in all the other databases, or as in java.sql.Date. Oracle’s DATE type is really a TIMESTAMP(0), i.e. a timestamp with a fractional second precision of zero.

Most legacy databases actually use DATE precisely for that, to store timestamps with no fractional seconds, such as:

  • 1970-01-01 00:00:00
  • 2000-02-20 20:00:20
  • 1337-01-01 13:37:00

So, it’s always a safe bet to use java.sql.Timestamp types in Java, when you’re operating with Oracle DATE.

But things can go very wrong when you bind such variables via JDBC as can be seen in this Stack Overflow question here. Let’s assume you have a range predicate like so:

// execute_at is of type DATE and there's an index
PreparedStatement stmt = connection.prepareStatement(
    "SELECT * " + 
    "FROM my_table " +
    "WHERE execute_at > ? AND execute_at < ?");

Now, naturally, we’d expect any index on execute_at to be a sensible choice to use for filtering out records from my_table, and that’s also what happens when we bind java.sql.Date

stmt.setDate(1, start);
stmt.setDate(2, end);

The execution plan is optimal:

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|*  1 |  FILTER                      |              |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table     |
|*  3 |    INDEX RANGE SCAN          | my_index     |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1:1 AND ""EXECUTE_AT""<:2)

But let’s check out what happens if we assume execute_at to be a date with hours/minutes/seconds, i.e. an Oracle DATE. We’ll be binding java.sql.Timestamp

stmt.setTimestamp(1, start);
stmt.setTimestamp(2, end);

and the execution plan suddenly becomes very bad:

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|*  1 |  FILTER                      |              |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table     |
|*  3 |    INDEX FULL SCAN           | my_index     |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1:1 AND 
               INTERNAL_FUNCTION(""EXECUTE_AT"")<:2))

What’s this INTERNAL_FUNCTION()

INTERNAL_FUNCTION() is Oracle’s way of silently converting values into other values in ways that are completely opaque. In fact, you cannot even place a function-based index on this pseudo-function to help the database choose a RANGE SCAN on it again. The following is not possible:

CREATE INDEX oracle_why_oh_why
  ON my_table(INTERNAL_FUNCTION(execute_at));

Nope. What the function really does, it widens the less precise DATE type of the execute_at column to the more precise TIMESTAMP type of the bind variable. Just in case.

Why? Because with exclusive range boundaries (> and <), chances are that the fractional seconds in your Timestamp may lead to the timestamp being stricly greater than the lower bound of the range, which would include it, when the same Timestamp with no fractional sections (i.e. an Oracle DATE) would have been excluded.

Duh. But we don’t care, we’re only using Timestamp as a silly workaround in the first place! Now that we know this, you might think that adding a function-based index on an explicit conversion would work, but that’s not the case either:

CREATE INDEX nope
  ON my_table(CAST(execute_at AS TIMESTAMP));

Perhaps, if you magically found the exact right precision of the implicitly used TIMESTAMP(n) type it could work, but that all feels shaky, and besides, I don’t want a second index on that same column!

The solution

The solution given by user APC is actually very simple (and it sucks). Again, you could bind a java.sql.Date, but that would make you lose all hour/minute/second information. No, you have to explicitly cast the bind variable to DATE in the database. Exactly!

PreparedStatement stmt = connection.prepareStatement(
    "SELECT * " + 
    "FROM my_table " +
    "WHERE execute_at > CAST(? AS DATE) " +
    "AND execute_at < CAST(? AS DATE)");

You have to do that every time you bind a java.sql.Timestamp variable to an Oracle DATE value, at least when used in predicates.

How to implement that?

If you’re using JDBC directly, you’re pretty much doomed. Of course, you could run AWR reports to find the worst statements in production and fix only those, but chances are that you won’t be able to fix your statements so easily and deploy them so quickly, so you might want to get it right in advance. And of course, this is production. Tomorrow, another statement would suddenly pop up in your DBA’s reports.

If you’re using JPA / Hibernate, you can only hope that they got it right, because you probably won’t be able to fix those queries, otherwise.

If you’re using jOOQ 3.5 or later, you can take advantage of jOOQ’s new custom type binding feature, which works out of the box with Oracle, and transparently renders that CAST(? AS DATE) for you, only on those columns that are really relevant.

jOOQ is the best way to write SQL in Java

Other databases

If you think that this is an Oracle issue, think again. Oracle is actually very lenient and nice to use when it comes to bind variables. Oracle can infer a lot of types for your bind variables, such that casting is almost never necessary. With other databases, that’s a different story. Read our article about RDBMS bind variable casting madness for more information.

You’re Very Likely to Have Gotten SQL Date Time Arithmetic Wrong!

You’re very likely to have gotten SQL date time arithmetic wrong. And why is that? Google it! You’ll quickly find blog posts like these:

And they’re all advocating stuff like this:

SYSDATE + (10/1440) is ten minutes from now.

Is it really? What about this beauty:

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
       trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
       trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min",
       trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs",
       trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days"
FROM emp;

Did you think about timezones? Did you think about daylight savings time? Did you think about leap seconds? And there many other things that can go horribly wrong, when you think you can beat date time arithmetic by counting seconds, days and other entities. Intead of adding things up by yourself, use vendor-specific built-in functions. Unfortunately, they’re a bit hard to remember.

But luckily, there’s jOOQ to standardise SQL. We’ve blogged about expression standardisation before, and we’re doing it again, with this simple date time arithmetic test programme:

import static org.jooq.DatePart.DAY;
import static org.jooq.DatePart.HOUR;
import static org.jooq.DatePart.MINUTE;
import static org.jooq.DatePart.MONTH;
import static org.jooq.DatePart.SECOND;
import static org.jooq.DatePart.YEAR;
import static org.jooq.SQLDialect.INGRES;
import static org.jooq.SQLDialect.SQL99;
import static org.jooq.impl.DSL.select;
import static org.jooq.impl.DSL.timestampAdd;
import static org.jooq.impl.DSL.using;

import java.sql.Timestamp;
import java.util.EnumSet;

import org.jooq.QueryPart;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;

public class Compatibility {

    public static void main(String[] args) {
        Timestamp t = new Timestamp(0);

        // Using the new version of the 
        // timestampAdd() function
        // that will be added in jOOQ 3.3
        print(select(
            timestampAdd(t, 2, YEAR)  .as("yy"),
            timestampAdd(t, 2, MONTH) .as("mm"),
            timestampAdd(t, 2, DAY)   .as("dd"),
            timestampAdd(t, 2, HOUR)  .as("hh"),
            timestampAdd(t, 2, MINUTE).as("mi"),
            timestampAdd(t, 2, SECOND).as("ss")
        ));
    }

    private static void print(QueryPart part) {
        System.out.println("Printing " + part);
        System.out.println("---------------------");

        // Get only SQLDialect families, don't
        // distinguish between
        // SQL Server 2008 or SQL Server 20012
        EnumSet<SQLDialect> dialects =
            EnumSet.noneOf(SQLDialect.class);
        for (SQLDialect dialect:SQLDialect.values())
            if (dialect != SQL99 && dialect != INGRES)
                dialects.add(dialect.family());

        // Render the passed in SQL clause to
        // all supported SQL dialects
        for (SQLDialect dialect: dialects)
            System.out.println(
                String.format("%1$s: \n%2$s\n",
                dialect, using(dialect, new Settings()
                         .withRenderFormatted(true))
                         .renderInlined(part)
            ));

        System.out.println();
        System.out.println();
    }
}

And here’s the output for most databases currently supported by jOOQ:

-- CUBRID: 
select 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 year) "yy", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 month) "mm", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 day) "dd", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 hour) "hh", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 minute) "mi", 
  date_add(datetime '1970-01-01 01:00:00.0', interval 2 second) "ss"
from "db_root"

-- DERBY: 
select 
  {fn timestampadd(sql_tsi_year, 2, timestamp('1970-01-01 01:00:00.0')) } as "yy", 
  {fn timestampadd(sql_tsi_month, 2, timestamp('1970-01-01 01:00:00.0')) } as "mm", 
  {fn timestampadd(sql_tsi_day, 2, timestamp('1970-01-01 01:00:00.0')) } as "dd", 
  {fn timestampadd(sql_tsi_hour, 2, timestamp('1970-01-01 01:00:00.0')) } as "hh", 
  {fn timestampadd(sql_tsi_minute, 2, timestamp('1970-01-01 01:00:00.0')) } as "mi", 
  {fn timestampadd(sql_tsi_second, 2, timestamp('1970-01-01 01:00:00.0')) } as "ss"
from "SYSIBM"."SYSDUMMY1"

-- FIREBIRD: 
select 
  dateadd(year, 2, timestamp '1970-01-01 01:00:00.0') "yy", 
  dateadd(month, 2, timestamp '1970-01-01 01:00:00.0') "mm", 
  dateadd(day, 2, timestamp '1970-01-01 01:00:00.0') "dd", 
  dateadd(hour, 2, timestamp '1970-01-01 01:00:00.0') "hh", 
  dateadd(minute, 2, timestamp '1970-01-01 01:00:00.0') "mi", 
  dateadd(second, 2, timestamp '1970-01-01 01:00:00.0') "ss"
from "RDB$DATABASE"

-- H2: 
select 
  dateadd('year', 2, timestamp '1970-01-01 01:00:00.0') "yy", 
  dateadd('month', 2, timestamp '1970-01-01 01:00:00.0') "mm", 
  dateadd('day', 2, timestamp '1970-01-01 01:00:00.0') "dd", 
  dateadd('hour', 2, timestamp '1970-01-01 01:00:00.0') "hh", 
  dateadd('minute', 2, timestamp '1970-01-01 01:00:00.0') "mi", 
  dateadd('second', 2, timestamp '1970-01-01 01:00:00.0') "ss"
from dual

-- HSQLDB: 
select 
  {fn timestampadd(sql_tsi_year, 2, timestamp '1970-01-01 01:00:00.0') } as "yy", 
  {fn timestampadd(sql_tsi_month, 2, timestamp '1970-01-01 01:00:00.0') } as "mm", 
  {fn timestampadd(sql_tsi_day, 2, timestamp '1970-01-01 01:00:00.0') } as "dd", 
  {fn timestampadd(sql_tsi_hour, 2, timestamp '1970-01-01 01:00:00.0') } as "hh", 
  {fn timestampadd(sql_tsi_minute, 2, timestamp '1970-01-01 01:00:00.0') } as "mi", 
  {fn timestampadd(sql_tsi_second, 2, timestamp '1970-01-01 01:00:00.0') } as "ss"
from "INFORMATION_SCHEMA"."SYSTEM_USERS"

-- MARIADB: 
select 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 year) as `yy`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 month) as `mm`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 day) as `dd`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 hour) as `hh`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 minute) as `mi`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 second) as `ss`
from dual

-- MYSQL: 
select 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 year) as `yy`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 month) as `mm`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 day) as `dd`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 hour) as `hh`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 minute) as `mi`, 
  date_add(timestamp '1970-01-01 01:00:00.0', interval 2 second) as `ss`
from dual

-- POSTGRES: 
select 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' year')::interval) as "yy", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' month')::interval) as "mm", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' day')::interval) as "dd", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' hour')::interval) as "hh", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' minute')::interval) as "mi", 
  (timestamp '1970-01-01 01:00:00.0' + (2 || ' second')::interval) as "ss"

-- SQLITE: 
select 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' year') yy, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' month') mm, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' day') dd, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' hour') hh, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' minute') mi, 
  datetime('1970-01-01 01:00:00.0', '+' || 2 || ' second') ss

-- DB2: 
select 
  (timestamp '1970-01-01 01:00:00.0' + 2 year) "yy", 
  (timestamp '1970-01-01 01:00:00.0' + 2 month) "mm", 
  (timestamp '1970-01-01 01:00:00.0' + 2 day) "dd", 
  (timestamp '1970-01-01 01:00:00.0' + 2 hour) "hh", 
  (timestamp '1970-01-01 01:00:00.0' + 2 minute) "mi", 
  (timestamp '1970-01-01 01:00:00.0' + 2 second) "ss"
from "SYSIBM"."DUAL"

-- ORACLE: 
select 
  (timestamp '1970-01-01 01:00:00.0' + numtoyminterval(2, 'year')) "yy", 
  (timestamp '1970-01-01 01:00:00.0' + numtoyminterval(2, 'month')) "mm", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'day')) "dd", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'hour')) "hh", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'minute')) "mi", 
  (timestamp '1970-01-01 01:00:00.0' + numtodsinterval(2, 'second')) "ss"
from dual

-- SQLSERVER: 
select 
  dateadd(yy, 2, '1970-01-01 01:00:00.0') [yy], 
  dateadd(mm, 2, '1970-01-01 01:00:00.0') [mm], 
  dateadd(dd, 2, '1970-01-01 01:00:00.0') [dd], 
  dateadd(hh, 2, '1970-01-01 01:00:00.0') [hh], 
  dateadd(mi, 2, '1970-01-01 01:00:00.0') [mi], 
  dateadd(ss, 2, '1970-01-01 01:00:00.0') [ss]

-- SYBASE: 
select 
  dateadd(yy, 2, '1970-01-01 01:00:00.0') [yy], 
  dateadd(mm, 2, '1970-01-01 01:00:00.0') [mm], 
  dateadd(dd, 2, '1970-01-01 01:00:00.0') [dd], 
  dateadd(hh, 2, '1970-01-01 01:00:00.0') [hh], 
  dateadd(mi, 2, '1970-01-01 01:00:00.0') [mi], 
  dateadd(ss, 2, '1970-01-01 01:00:00.0') [ss]
from [SYS].[DUMMY]

If you’ve read thus far, you’ve realised that you shouldn’t perform date-time arithmetic using fractional days as many people think they should when they write Oracle SQL. Just use built-in functions and/or interval data types. Always!