0

Ninox Date and Time Issues (continued)

I have a date on a form set for January 17, 2023.  I have a view looking up Transactions from a table with the filter of 

let xDate := datetime(Date);
let xNewDate := extractDate(xDate, -6);
let xTime := Time;
select Transactions where JOBS.Id = 3979 and extractDate('Date / Time', 6) = xNewDate and time('Date / Time') < xTime

I still have 01/16/2023 4:10 pm showing up.  This is the previous day.  

Any suggestions would be appreciated.  

11 replies

null
    • Nick_Massie
    • 1 yr ago
    • Reported - view

    In addition I am trying to filter all records before 12:00 pm and All records after 12:00 pm and I get all the records for that day.  

    • Nick_Massie
    • 1 yr ago
    • Reported - view

    The Time field is statically set to 12:00 pm 

    • Fred
    • 1 yr ago
    • Reported - view

    So you are trying to filter records equal to Jan 17, 2023 but before 12:00pm?

    what is the code behind your extractDate() function?

    When I troubleshoot a select filter I use another formula field and breakdown each filter.

    So does:

    select Transactions where JOBS.Id = 3979

    return the expected records?

    Does:

    select Transactions where JOBS.Id = 3979 and extractDate('Date / Time', 6) = xNewDate

    return the expected records?

    • Nick_Massie
    • 1 yr ago
    • Reported - view

    I just enter the Date that I want for the report.  

    • Nick_Massie
    • 1 yr ago
    • Reported - view

    So you are trying to filter records equal to Jan 17, 2023 but before 12:00pm?

    what is the code behind your extractDate() function?

    When I troubleshoot a select filter I use another formula field and breakdown each filter.

    So does:

    select Transactions where JOBS.Id = 3979

    return the expected records? Yes

    Does:

    select Transactions where JOBS.Id = 3979 and extractDate('Date / Time', 6) = xNewDate

    return the expected records? Yes all of the + January 16

    • Fred
    • 1 yr ago
    • Reported - view

    Is Time a time field? or a number field?

    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view
    Nick Massie said:
    let xDate := datetime(Date); let xNewDate := extractDate(xDate, -6); let xTime := Time; select Transactions where JOBS.Id = 3979 and extractDate('Date / Time', 6) = xNewDate and time('Date / Time') < xTime

    Why do you use extractDate once with -6 and another time with +6?

    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view
    Fred said:
    what is the code behind your extractDate() function?

     ExtractDate returns any date in a specific time zone. It allows to correct the date operations of the server according to the universal time zone. When datetime calculations are done locally they use the local time zone, when they are done on the ninox server, they use the universal time zone. When you mix the two, you get shifted results

      • Fred
      • 1 yr ago
      • Reported - view

      Jacques TUR as always, thanks for the info about a Ninox function that is not documented, or easy to find. 

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      Fred Sorry, this is not a Ninox feature. I did this in collaboration with Nick Massie Nick Massie when we were trying to fix some date shifts in Ninox.

      "------------------  convert datetime to defined timezone and clear time part.  -------------------";
function extractDate(d : datetime,timeZone : number) do
    "// add the time zone to the numerical value of the universal date";
    var n := number(d) + timeZone * 60 * 60 * 1000;

    "// fills the time part to 0 and returns a datetime with only day, month and year.";
    datetime(floor(n / (24 * 60 * 60 * 1000)) * (24 * 60 * 60 * 1000))
end;

      extractDate is actually the name of the first function I did to try to solve the problem. It has remained although the function is now different. It would be better to give it a name like GetDateInTimeZone.

      • Nick_Massie
      • 1 yr ago
      • Reported - view

      Jacques TUR As always you are a wonder!  Thank you for this.  I will get to work on this right now.  

Login to reply

Content aside

  • 1 yr agoLast active
  • 11Replies
  • 151Views
  • 3 Following