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)

kara · Accepted Answer · 2018-05-08T05:44:40.970

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;

Thank you, this link was very helpful. It's perfect except it returns the actual timezone too. Do you know how I can leave that out (the EUROPE/BERLIN text) of the result? Thanks! — dukenukem6487, May 07 '18 at 15:04
Added an example-query and some explainaition for the pattern. — kara, May 08 '18 at 05:45

score -1 · Answer 2 · answered May 07 '18 at 13:29

-1

You can try this :

SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') AS myDate
FROM   dual;

For more information read this document.

answered May 07 '18 at 13:29

Mahmoud

325
10
25

This only selects the string o_O – kara May 07 '18 at 13:31
He can change the pattern with the value he wants – Mahmoud May 07 '18 at 13:33
He did not ask for a string. He wants to add 4 hours to his date. – kara May 07 '18 at 13:38
he said _It should be 1pm but says 5pm because of UTC_ and _so I need to subtract 4 hours from it_ Because his request does not return the good value. He asks for a good way of doing things. so it is not necessary to subtract 4 hours to have the good result. – Mahmoud May 07 '18 at 13:43
But your solution `TO_CHAR()` does only return the wrong value as `string`/`varchar`. The some wrong value without any change. – kara May 07 '18 at 13:47

Subtracting hours from Unix Timestamp

2 Answers2