All Forums Database
dsreddy117 20 posts Joined 11/07
20 Nov 2007
How to cast the timestamp

Hi All,I have a column in the table with timestamp(0) data type. My requirement is to extract the time portion of this column and compare if it is between 00:00:00 to 04:59:00 i.e upto morning 5 am.create table entry(start_tmstp timestamp(0) format 'yyyy-mm-dd hh:mis:ss',other columns...)select * from entrywhere start_tmstp between 00:00:00 to 04:59:59Can you please help me in completing the above SQL? Thanks in advance.RegardsRahul

Fred 1096 posts Joined 08/04
20 Nov 2007

where cast(start_tmstp as time(0)) between time'00:00:00' and time'04:59:59'

Fred 1096 posts Joined 08/04
20 Nov 2007

On second thought, comparing TIME fields can be tricky if the current "session time zone offset" is nonzero. In general, you are probably more likely to get the results you expect if you CAST to CHAR or EXTRACT the time components and do a numeric comparision. For example:where cast(cast(start_tmstp as time(0) format 'hh:mi:ss') as char(8)) between '00:00:00' and '04:59:59' where 10000*extract(hour from start_tmstp)+100*extract(minute from start_tmstp)+extract(second from start_tmstp) between 000000 and 045959or even, for this specific case, justwhere extract(hour from start_tmstp) between 0 and 4

dnoeth 4628 posts Joined 11/04
21 Nov 2007

Hi Fred,is the session time zone is set, then he'd better use TIMESTAMP WITH TIME ZONE :-)Casts to time normalize the time, but you can de-normalize it, too:SET TIME ZONE INTERVAL '03:00' HOUR TO MINUTE;SELECT CAST(start_tmstp AS TIME(0)) ,CAST(start_tmstp AS TIME(0)) AT TIME ZONE INTERVAL '00:00' HOUR TO MINUTE FROM entry;start_tmstp start_tmstp AT TIME ZONE INTERVAL 0:00 HOUR TO MINUTE----------- ------------------------------------------------------ 12:23:34 09:23:34+00:00 18:23:34 15:23:34+00:00SET TIME ZONE INTERVAL -'03:00' HOUR TO MINUTE;SELECT CAST(start_tmstp AS TIME(0)) ,CAST(start_tmstp AS TIME(0)) AT TIME ZONE INTERVAL '00:00' HOUR TO MINUTE FROM entry;start_tmstp start_tmstp AT TIME ZONE INTERVAL 0:00 HOUR TO MINUTE----------- ------------------------------------------------------ 06:23:34 09:23:34+00:00 12:23:34 15:23:34+00:00Dieter

Dieter

26 Nov 2007

sel* from entry where start_tmstp in ( case when cast(cast(extract( hour from start_tmstp)||':'||Trim(extract( Minute from start_tmstp))||':'||Trim(extract( second from start_tmstp)) as time(6))-cast('12:00:00' as time(6)) as time(6)) < cast('5:00:00' as time(6)) then 'Y' else 'N' end)Time is 12:00 is taken because (file arrival time - 12:00 )should not be greater than 5 hours .

You must sign in to leave a comment.