How to specify timestamp format in Oracle SQL

Question

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

Please post sample data and your desired output. I have edited your question to format it, please take some time and follow posting guidelines. Please see How to ask a question — Lalit Kumar B
– Lalit Kumar B, Commented May 8, 2020 at 18:42

Littlefoot · Accepted Answer · 2020-05-08 19:06:55Z

2

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>

answered May 8, 2020 at 19:06

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JOE Over a year ago

Tho for little interest how do you deal with minutes when they are single digit like 3:2?

Littlefoot Over a year ago

You're welcome. As of your last question: EXTRACT would extract them as well, no difference. If you meant how to format it so that e.g. 2 minutes are displayed as 02 (with a leading zero), then use LPAD: lpad(extract(minute from diff), 2, '0')

Collectives™ on Stack Overflow

How to specify timestamp format in Oracle SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related