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

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
    599 Posts

    Re: Better way to format date?

    ‏2013-10-10T21:09:12Z  

    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
    3 Posts

    Re: Better way to format date?

    ‏2013-10-11T14:59:58Z  

    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

  • MBARTOSH
    MBARTOSH
    12 Posts

    Re: Better way to format date?

    ‏2013-10-29T00:24:54Z  
    • AndyKay
    • ‏2013-10-11T14:59:58Z

    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

    Andy,

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

  • AndyKay
    AndyKay
    3 Posts

    Re: Better way to format date?

    ‏2013-10-29T08:20:06Z  
    • MBARTOSH
    • ‏2013-10-29T00:24:54Z

    Andy,

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

    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
    599 Posts

    Re: Better way to format date?

    ‏2013-10-29T19:09:04Z  
    • AndyKay
    • ‏2013-10-11T14:59:58Z

    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

    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