How to cast the timestamp

Database
Enthusiast

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 entry
where start_tmstp between 00:00:00 to 04:59:59

Can you please help me in completing the above SQL? Thanks in advance.

Regards
Rahul
4 REPLIES
Teradata Employee

Re: How to cast the timestamp

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

Re: How to cast the timestamp

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 045959

or even, for this specific case, just
where extract(hour from start_tmstp) between 0 and 4
Junior Contributor

Re: How to cast the timestamp

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

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
----------- ------------------------------------------------------
06:23:34 09:23:34+00:00
12:23:34 15:23:34+00:00

Dieter
Enthusiast

Re: How to cast the timestamp

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 .