CODEWITHSUNDEEP
sqloracle-databasedatedatetimetimestamp
JOE
JOE

Reputation: 47

How to specify timestamp format in Oracle SQL

It shows it in very bad way but I need to be Hour:Minutes. 

SELECT DepName, ARRSTATIONNAME,  VEHICLENUMBER, ARRVTIME - DEPTIME FROM VEHICLENUMBER...

ARRVTIME - DEPTIME is where I need to do formatting because rn I am getting

+000000000 02:28:00.000000

Upvotes: 0

Views: 57

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Looks like you're dealing with timestamps. If that's so, have a look at this example:

Sample table:

SQL> create table test (arrvtime timestamp, deptime timestamp);

Table created.

SQL> insert into test values (systimestamp, systimestamp - 0.2);

1 row created.

SQL> select * From test;

ARRVTIME                        DEPTIME
------------------------------- -------------------------------
08.05.20 21:04:50,508000        08.05.20 16:16:50,000000

Query you might need:

SQL> select arrvtime - deptime diff,
  2         extract(hour from arrvtime - deptime) hours,
  3         extract(minute from arrvtime - deptime) minutes,
  4         --
  5         -- what you want
  6         extract(hour  from arrvtime - deptime) ||':'||
  7         extract(minute from arrvtime - deptime) result
  8  from test;

DIFF                                 HOURS    MINUTES RESULT
------------------------------- ---------- ---------- ------
+000000000 04:48:00.508000               4         48 4:48

SQL>

Upvotes: 2

Related Questions