topic Re: How to select a Day from a Date. in App Development

How to select a Day from a Date.

StaHorse — Fri, 15 Nov 2024 22:02:00 GMT

Hi,

I have this expression "where num(Date(LogTimeStamp)) >= num(monthstart(AddMonths(date(today()),-2)));" I need to change it to look only at only the previous day. please help.

Re: How to select a Day from a Date.

BrunPierre — Mon, 06 Mar 2023 11:49:29 GMT

Hi,

Filter for only the previous day data as below.

"where num(Date(LogTimeStamp)) = Num(Date(Today()-1)) ;"

Re: How to select a Day from a Date.

StaHorse — Mon, 06 Mar 2023 15:35:09 GMT

Hi,

There's my date:

and when I put this condition "where num(Date(LogTimeStamp)) = Num(Date(Today()-1)) ;" I get nothing back. I expect to get all the data for the 05th, which is a day before today (06 March)

Re: How to select a Day from a Date.

BrunPierre — Mon, 06 Mar 2023 16:57:03 GMT

Try with this.

Where Date(Timestamp#(ErrorTime, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') = Date(Today()-1, 'YYYY-MM-DD')

Re: How to select a Day from a Date.

StaHorse — Tue, 07 Mar 2023 06:34:50 GMT

Hi,

I still get empty results:

Here's my query:

FailedReloads:
LOAD
Id,
LogTimeStamp as ErrorTime,
Hostname as ErrorHost,
"Task Name" as FailedTask,
"App Name" as FailedApp,
EndStatus as Failed,
Message as Message,
rowno() as FailedRow
resident TCheckReloadTasks
where Date(Timestamp(LogTimeStamp, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') = Date(Today()-1, 'YYYY-MM-DD')
;

Re: How to select a Day from a Date.

Or — Tue, 07 Mar 2023 06:42:19 GMT

Date() does not trim the time from a timestamp. If you want to trim the time aspect, use Floor(Date) or Dayname(Date).

Re: How to select a Day from a Date.

StaHorse — Tue, 07 Mar 2023 06:46:43 GMT

I tried both Floor and DayName, still no results:

FailedReloads:
LOAD
Id,
LogTimeStamp as ErrorTime,
Hostname as ErrorHost,
"Task Name" as FailedTask,
"App Name" as FailedApp,
EndStatus as Failed,
Message as Message,
rowno() as FailedRow
resident TCheckReloadTasks
where Floor(Timestamp(LogTimeStamp, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') = Floor(Today()-1, 'YYYY-MM-DD')

Re: How to select a Day from a Date.

Or — Tue, 07 Mar 2023 06:54:37 GMT

That doesn't look like the correct use of Floor()... why did you give it a YYYY-MM-DD parameter? Floor is a mathematical function...

Re: How to select a Day from a Date.

StaHorse — Tue, 07 Mar 2023 07:24:47 GMT

I solved it, I did this instead:

let vCurentMonth = Month(addmonths(today(),-0));
let vPreviousDay = Day(Today()-1);

FailedReloads:
LOAD
Id,
LogTimeStamp as ErrorTime,
Day(LogTimeStamp) as LogDay,
month(LogTimeStamp) as LogMonth,
Hostname as ErrorHost,
"Task Name" as FailedTask,
"App Name" as FailedApp,
EndStatus as Failed,
Message as Message,
rowno() as FailedRow
resident TCheckReloadTasks
where Day(LogTimeStamp) = '$(vPreviousDay)'
and month(LogTimeStamp) = '$(vCurentMonth)'