All Forums Database
sk8s3i 35 posts Joined 06/13
24 Sep 2014
Timestamp format

I have a timestamp(6) field which holds values like: 2011-09-08 15:10:00.000000
I need to get this timestamp as: 2011-09-08 15:00:00.000000
I want to avoid SUBSTR and alike fucntions and do this in one shot. Is not there any timestamp format though which we can accomplish this?
 
Regards,
Shardul

-Thanks Shardul

24 Sep 2014

Hi Shardul,
what i understand from ur question is, u want to display a timestamp like 2011-09-08 15:10:00.000000.
You could simply run the query as given in below format. 
SEL TIMESTAMP '2011-09-08 15:10:00.000000'.
 
Thanks
Jugal

Raja_KT 1246 posts Joined 07/09
24 Sep 2014

One way I can see is concatenate date and extract hour and concatenate with ||':00:00.000000')
Another way is
SELECT CAST(current_date AS TIMESTAMP(0)) + CAST(EXTRACT(HOUR FROMcurrent_timestamp)
AS INTERVAL HOUR(2).......);

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
24 Sep 2014

Hi Shardul ,
This may help you
SELECT CAST(CURRENT_DATE AS TIMESTAMP(6));
SELECT CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS TIMESTAMP(6));
Thanks !!!
 
 
 
 

SAP

sk8s3i 35 posts Joined 06/13
24 Sep 2014

Hi Raja,
Thanks for your input.
My field, say myDateTS, is TIMESTAMP(6). So, I guess we would not be able to cast it to TIMESTAMP(0). We can then cast it to date and then again to timestamp(0) and then the other things. I would like to avoid these many CAST operations, if we can :)
 
Moreover, for some comparision like below, without casting it to timestamp(6), the expression will evaluate to false.
 
 
select CASE when '2014-09-24 04:00:00.0' = '2014-09-24 04:00:00.000000' then 1
 
else 2 end as euki;
Please help!
 
Regards,
Shardul

-Thanks Shardul

Raja_KT 1246 posts Joined 07/09
24 Sep 2014

How about the first one?
One way I can see is concatenate date and extract hour and concatenate with ||':00:00.000000') ?
You don't like it? It is a bit dirty :)

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sk8s3i 35 posts Joined 06/13
24 Sep 2014

Yeah Raja, the first idea, of conacatenating with ':00:00.000000', I had it earlier :)
Finally I am going with something like below:
SELECT CAST(CAST(myDateDTS as DATE format 'YYYY-MM-DD') AS TIMESTAMP(6))
 + CAST(EXTRACT(HOUR FROM myDateDTS) AS INTERVAL HOUR(2)) as anotherDTS
from dbname.tablename;

 
Please do comment if you have any better way of doing the same.
 
Regards,
Shardul

-Thanks Shardul

tdice 55 posts Joined 01/11
24 Sep 2014

you may check the below query and see which one is good from performance perspctive.

 

select  cast (cast(columnname as date format 'yyyy-mm-dd')||' '||trim(extract(hour from columnname))||':00:00.000000'  as timestamp(6) ) 

 from  tablename;

 

Thanks

24 Sep 2014

Hi Shardul,
 
You could also try the below query,
SEL CAST(CAST(CURRENT_TIMESTAMP AS FORMAT 'YYYY-MM-DD b HH:MI:SS') AS TIMESTAMP(6)) - INTERVAL '1' MINUTE * (EXTRACT(MINUTE FROM (CAST(CAST(CURRENT_TIMESTAMP AS FORMAT 'HH:MI:SS') AS TIMESTAMP(6)))))
Current TIme:
9/24/2014 12:28:22.950000
After Calc:
9/24/2014 12:00:51.190000
 
 
Thanks
Jugal

You must sign in to leave a comment.