MS SQL time functions are not working on Vertica

Hi Hibiki,

Please find the query and error screenshot

SELECT right(cast(time_created as date),10) as 'Day',
event_id as Events
FROM opr_event
where time_created >= convert(datetime,convert(varchar(8),getdate()-7,112))
group by cast(time_created as date);

Hi Hibiki,

Please check the below error screenshot.

Hi Hibiki,

Please find the below error error.

SELECT to_char(time_created,'YYYY-MM-DD') as 'Day', event_id as Events
FROM opr_event
WHERE time_created >= trunc(getdate()-7,'DD')
group by to_char(time_created,'YYYY-MM-DD');

Then, I modified the query as below. But GROUP BY is not working as expected.

SELECT to_char(time_created,'YYYY-MM-DD') as 'Day', count(event_id) as Event_Count
FROM opr_event
WHERE time_created >= trunc(getdate()-7,'DD')
group by to_char(time_created,'YYYY-MM-DD'), opr_event.event_id;

After adding the ORDER BY then the issue re-occurred.

SELECT to_char(time_created,'YYYY-MM-DD') as 'Day', count(event_id) as Event_Count
FROM opr_event
WHERE time_created >= trunc(getdate()-7,'DD')
group by to_char(opr_event.time_created,'YYYY-MM-DD'), opr_event.event_id
order by opr_event.time_created desc;

Hi Hibiki,

Yes, now its working fine. Thanks a lot and appreciating your effort and quick help.

May I know what time functions should I use if I want to query for weeks, months and years?

We are looking for historical event and metric data for POC Project.

Hi Hibiki,

Below is the exact query which I was looking for.

SELECT to_char(time_created,'YYYY-MM-DD') as 'Day', count(distinct event_id) as Events
FROM opr_event
WHERE time_created >= trunc(getdate()-7,'DD')
group by to_char(time_created,'YYYY-MM-DD')
order by to_char(time_created,'YYYY-MM-DD') desc;

Meanwhile, could you please clarify the need of usage of below syntax.

to_char(time_created,'YYYY-MM-DD')