CODEWITHSUNDEEP
sqloracle-database
Toddler
Toddler

Reputation: 95

Extract time from a column

I've a column like last_located_time which contain values like 2017-05-13T17:33:36.000+0000.

I have tried to remove only time, but no luck.

SELECT USERNAME, TO_DATE(SUBSTR(LAST_LOCATED_TIME,11,17),'HH:MI:SS') "lAST TIME"
FROM Tb_089

How should I extract only time value from the columns for all users?

Thanks in advance!

Upvotes: 0

Views: 119

Answers (2)

MT0
MT0

Reputation: 168796

If you want the time component in the UTC time zone (so all times are being displayed in a common time zone) then:

(assuming you have a TIMESTAMP WITH TIME ZONE data type)

SELECT TO_CHAR(
         last_located_time AT TIME ZONE 'UTC',
         'HH24:MI:SS'
       )
FROM   Tb_089;

If you, instead, the column is an ISO 8601 formatted string:

SELECT TO_CHAR(
         TO_TIMESTAMP_TZ(
           last_located_time,
           'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'
         ) AT TIME ZONE 'UTC',
         'HH24:MI:SS'
       )
FROM   Tb_089;

If you want the time component as an interval:

SELECT CAST( utc_last_located_time AS TIMESTAMP ) - TRUNC( utc_last_located_time )
         AS time_interval
FROM   (
  SELECT TO_TIMESTAMP_TZ(
           last_located_time,
           'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'
         ) AT TIME ZONE 'UTC' AS utc_last_located_time
  FROM   Tb_089
);

If you want the time component of the string (without adjusting for disparate time zones) then you could just do:

SELECT SUBSTR( last_located_time, 12, 8 )
FROM   Tb_089

Upvotes: 2

Prateek
Prateek

Reputation: 76

To_Date is used to convert Char and Varchar2 into Date.

You need to use To_Char which is opposite of To_Date

Select To_Char(LAST_LOCATED_TIME, 'HH24:MI:SS') "Last Time" from Tb_089;

Upvotes: 0

Related Questions