Skip to Main Content
  • Questions

  • Time Stamp in HH:MI AM format while fetching return 4 hours ahead of the stored time

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pavan.

Asked: August 17, 2017 - 2:50 pm UTC

Last updated: August 21, 2017 - 11:13 am UTC

Version: 4.0.3

Viewed 10K+ times! This question is

You Asked

Hi Tom/Team,

I have a table with the timestamp of HH:MI AM format. While storing the time to the DB, it stores as 01-AUG-17 06.00.00.000000000 AM.
I live in Delhi and my DB is on remote location.

Questions are:
1. 01-Aug-17 is stored for all values which should not be stored.
2. While fetching this field, the time is automatically incremented by 4 hours and returned.(In this case 10:00 am).I need this to be returned as 6:00 am itself.

Can you guys please help me out in solving these two issues?

Thanks,
Pavan

and Chris said...

1. Oracle Database doesn't have a time data type. You have to use either a timestamp or date which always include the date component.

2. Sounds like you have time zone conversion issues. Give us a test case showing the problem (create table + insert into + select) and we'll see what we can do to help.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Timestamp

Pavan Kumar V, August 17, 2017 - 4:36 pm UTC

1. I have used to_date - TO_DATE('11/22/2017','MM/DD/YYYY').
2. I have used to_timestamp - to_timestamp('10:00 AM','HH:MI AM').
The query is like this:
insert into prc_schedule(sid,pid,schedule_time,schedule_date,schedule_duration,schedule_type,schedule_status) values(10277,10277,to_timestamp('10:00 AM','HH:MI AM'),TO_DATE('11/22/2017','MM/DD/YYYY'),'1','On-Cycle','Approved');
a. Insertion and retrieval completely works fine except that to_timestamp will also store 01-Aug-2017 in every case.
b. While retrieving in Java- select * from prc_schedule where sid = 10277; gives me the time as 2017-08-01 14:00:00Z. while I actually need as 10:00 AM itself.

Thanks,
Pavan


Chris Saxon
August 21, 2017 - 11:13 am UTC

Like I said, timestamps always have a date component. If you to_timestamp a "time", Oracle Database defaults the date to the start of the current month:

alter system set fixed_date = '2015-01-28 00:00:00';
select sysdate, to_timestamp( '10:00 AM','HH:MI AM' ) t from dual;

SYSDATE               T                               
28-JAN-2015 00:00:00  01-JAN-2015 10.00.00.000000000  

alter system set fixed_date = none;
select sysdate, to_timestamp( '10:00 AM','HH:MI AM' ) t from dual;

SYSDATE               T                               
21-AUG-2017 04:07:36  01-AUG-2017 10.00.00.000000000


Specify a date explicitly to so you store the "correct" date (whatever that is).

If you want to get just the time back, you have a few options:

- to_char the columns in your query
- Store the times in a varcahr2 to start with
- Use a virtual column and extract the time in that

e.g.:

create table t (
  ts timestamp, 
  tm varchar2(10),
  ts_vc as ( to_char ( ts, 'hh:mi am' ) )
);

insert into t (ts, tm)
values (systimestamp, to_char(systimestamp, 'hh:mi am'));

select to_char(ts, 'hh:mi am'), tm, ts_vc from t;

TO_CHAR(TS,'HH:MIAM')  TM        TS_VC     
04:12 am               04:12 AM  04:12 am

Timestamp

Pavan Kumar, August 21, 2017 - 11:24 am UTC

Hey Chris,

Thank you so much for the solution. It was very helpful.

Regards,
Pavan
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.