CODEWITHSUNDEEP
regexoracle-databasesubstring
user1834616
user1834616

Reputation: 33

Converting a time stamp string into a number in Oracle

I have a column of time stamps being stored as varchars in the format HH:MM:SS and I need to find out how many seconds each time stamp contains.

Is there a way I can use a regular expression to get the values before each colon and store them as separate numerical values?

Essentially, I need an expression which splits 16:38:14 into:

HH | MM | SS

16 | 38 | 14

Thank you for any help or advice you can give.

Upvotes: 0

Views: 67

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

At first place, it is a bad idea to store DATETIME as VARCHAR2 data type. You should always use DATE or TIMESTAMP. A date data type has a date and time portion. It is never too late to change the design and get rid of the flaw.

With DATE/TIMESTAMP data type, All you need is TO_CHAR to display the date in your desired format.

SQL> alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
12/04/2014 10:38:56

SQL> select to_char(sysdate, 'hh24:mi:ss') as "time_portion" from dual;

time_por
--------
10:39:21

SQL>

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use substr():

select substr(val, 1, 2) as hh, substr(val, 4, 2) as mm, substr(val, 7, 2) as ss

If you want these as numbers, then use to_number() as well:

select to_number(substr(val, 1, 2)) as hh,
       to_number(substr(val, 4, 2)) as mm,
       to_number(substr(val, 7, 2)) as ss

Upvotes: 1

Related Questions