Better way to format date?

(imported topic written by JohnnyRapid)

I am trying to format a date such that…

  • Timezone is UTC
  • Be in a form SQLite can accept (e.g. YYYY-MM-DD HH:MM:SS).

The following is an example of the formatting method I am currently employing, it returns the last reported time of each computer.

(

(

(


  year of it as string 


  & "-" & month of it as two digits


  & "-" & day_of_month of it as two digits


) of date ("ut" as time zone) of it


& " " & time of time ("ut" as time zone) of it as string

) of last report time of it

)

of

(

bes computers

)

Note, the actual query has multiple items in the “of” clause making accessing “last report time of it” even more rebose.

Does anyone have formatting tips or suggestions? I can’t believe this is the best way of doing it.

(imported comment written by Lee Wei)

Johhny,

Yes, this is the way to do the formatting in Relevance Language.

You are not missing anything and your solution is right on.

Lee Wei

(imported comment written by AndyKay)

I’m also looking for a better way to report the last reported time in Web Reports. The Date format used by the console is fine, however the format used in Web Reports (reporting the name of the month in a text string) makes it virtually useless when exported to a CSV - how can I get the date in web reports in a numerical format? forgive me I am somewhat of a novice in this area, could I make use of the code above?

Thanks,

Andy

(imported comment written by MBARTOSH)

Andy,

Did you ever get an answer to this problem? I am trying to figure it out.

(imported comment written by AndyKay)

Hi,

I used the following as an analysis and used it in reports instead of the inbuilt one:

((day_of_month of it as two digits & “/” & month of it as two digits & “/” & year of it as string) of date(local time zone) of it & " "& (two digit hour of it as string & “:” & two digit minute of it as string & “:” & two digit second of it as string) of time (local time zone) of it) of now

Hope this helps :slight_smile:

Andy

(imported comment written by Lee Wei)

Yeah, I agree this is not ideal.

If we were to write a custom report, now we have to deal with the export to CSV and probably cannot use the built-in mechanism.

When I wrote the Excel Connector, I did convert the obvious date fields (such as Last Report Time) to a time format in Excel. So you can sort or even reformat as appropriate.

Lee Wei