Subtracting hours from Unix Timestamp

Question

The following query selects a unix timestamp. It should be 1pm but says 5pm because of UTC. It needs to be Eastern time 1pm, so I need to subtract 4 hours from it. What's the best way to go about this?

SELECT CAST(to_date('1970-01-01', 'YYYY-MM-DD') + substr(STARTTIME,0,10)/60/60/24 as timestamp)

Answer 1

You shouldn't caluclate manually. You should add or substract the timezone.

Oracle how to convert time in UTC to the local time (offset information is missing)

Here an example-query:

SELECT TO_CHAR (
           FROM_TZ (CAST (SYSDATE AS TIMESTAMP), 'UTC')
               AT TIME ZONE 'EUROPE/BERLIN',
           'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR')
           AS BERLIN_Time_complete,
           TO_CHAR (
           FROM_TZ (CAST (SYSDATE AS TIMESTAMP), 'UTC')
               AT TIME ZONE 'UTC',
           'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR')
           AS UTC_Complete,
           TO_CHAR (
           FROM_TZ (CAST (SYSDATE AS TIMESTAMP), 'UTC')
               AT TIME ZONE 'EUROPE/BERLIN',
           'YYYY-MM-DD HH24:MI:SS TZH:TZM')
           AS BERLIN_Time_complete,
           TO_CHAR (
           FROM_TZ (CAST (SYSDATE AS TIMESTAMP), 'UTC')
               AT TIME ZONE 'EUROPE/BERLIN',
           'TZH:TZM')
           AS BERLIN_Timezone,
           TO_CHAR (
           FROM_TZ (CAST (SYSDATE AS TIMESTAMP), 'UTC')
               AT TIME ZONE 'EUROPE/BERLIN',
           'TZR')
           AS Timezone_Name
  FROM DUAL;

The key is for Format:

• YYYY: Year with four digits (0000-9999 ex: 2018)
• MM: Month with two digits (01-12)
• DD: Day with two digits (01-31)
• HH24: Hour 00-23
• MI: Minutes 00-59
• SS: Seconds 00-59
• TZH: Timezone-Hours
• TZM: Timezone-Minutes (There are timezones with 30mins offset)
• TZR: Name of the timezone

You should play around with those, to understand the to_date()/to_char(). You'll need it.

If you realy want to add hours. Here an example:

select sysdate + INTERVAL '2' HOUR from dual;