Topic
5 replies Latest Post - ‏2013-10-29T19:09:04Z by Lee Wei
JohnnyRapid
JohnnyRapid
7 Posts
ACCEPTED ANSWER

Pinned topic Better way to format date?

‏2013-10-10T20:24:08Z |

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.

 

 

  • Lee Wei
    Lee Wei
    598 Posts
    ACCEPTED ANSWER

    Re: Better way to format date?

    ‏2013-10-10T21:09:12Z  in response to JohnnyRapid

    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

  • AndyKay
    AndyKay
    2 Posts
    ACCEPTED ANSWER

    Re: Better way to format date?

    ‏2013-10-11T14:59:58Z  in response to JohnnyRapid

    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

    Updated on 2013-10-11T15:01:24Z at 2013-10-11T15:01:24Z by AndyKay
    • MBARTOSH
      MBARTOSH
      12 Posts
      ACCEPTED ANSWER

      Re: Better way to format date?

      ‏2013-10-29T00:24:54Z  in response to AndyKay

      Andy,

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

      • AndyKay
        AndyKay
        2 Posts
        ACCEPTED ANSWER

        Re: Better way to format date?

        ‏2013-10-29T08:20:06Z  in response to MBARTOSH

        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 :)

         

        Andy

    • Lee Wei
      Lee Wei
      598 Posts
      ACCEPTED ANSWER

      Re: Better way to format date?

      ‏2013-10-29T19:09:04Z  in response to AndyKay

      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