Timestamp format

Database
Enthusiast

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

Tags (1)
8 REPLIES
Enthusiast

Re: Timestamp format

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

Enthusiast

Re: Timestamp format

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).......);

SAP
Enthusiast

Re: Timestamp format

Hi Shardul ,

This may help you

SELECT CAST(CURRENT_DATE AS TIMESTAMP(6));

SELECT CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS TIMESTAMP(6));

Thanks !!!

Enthusiast

Re: Timestamp format

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

Enthusiast

Re: Timestamp format

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 :)

Enthusiast

Re: Timestamp format

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

Enthusiast

Re: Timestamp format

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

Enthusiast

Re: Timestamp format

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