Different results with current_timestamp

[SourMylk]

Hi all,

These 2 scripts give back different results:

With timestamp:

select distinct events.node_name, events.status, nodes.platform_name from events, nodes where events.node_name=nodes.node_name and events.status<>'Completed' 
and events.status<>'Future' and events.scheduled_start>current_timestamp-1 day

With date:

select distinct events.node_name, events.status, nodes.platform_name from events, nodes where events.node_name=nodes.node_name and events.status<>'Completed' 
and events.status<>'Future' and events.scheduled_start>'2012-03-28'

How could I get the timestamp one to work?
It's like it doesn't go back after midnight.

[moon-buddy]

What TSM Sever version are you running?

[SourMylk]

TSM version 5.5

[ChrisRees]

Hi

I thnk this will work

Change the last part to be

date(events.scheduled_start)>='2012-03-28'

Cheers

[SourMylk]

Hi,

My problem is that I want to use the current_timestamp, so I don't have to rewrite the date every time I want to use the script.

[ChrisRees]

Hi

try this,

select distinct events.node_name, events.status, nodes.platform_name from events, nodes where events.node_name=nodes.node_name and events.status<>'Completed' and events.status<>'Future' and events.scheduled_start>='2012-01-01' and scheduled_start >= current_timestamp - 24 hours

Hope that helps

[SourMylk]

select distinct events.node_name, events.status, nodes.platform_name from events, nodes where events.node_name=nodes.node_name and events.status<>'Completed' and events.status<>'Future' and events.scheduled_start>='2012-01-01' and scheduled_start >= current_timestamp - 24 hours

This one works!
Thank you!

[SourMylk]

But I don't really understand why this works