3 Replies Latest reply on Jan 6, 2012 8:32 AM by RGBurghardt

    CFpop and Time Zone

    plarts Level 1

      Sorting by date the mail coming from CFpop, (via a query of query)

      The displayed dateTime is the sender dateTime (from sending location).

      How to get the arrival location dateTime ?


      The variable "date" of cfpop contains the sender dateTime.

      So the order is false because of the Time zone which is different.


      Thanks for any help.

        • 1. Re: CFpop and Time Zone
          plarts Level 1

          I answer myself, cause I found a solution. Here is the code to add a good date/time in the query and sort on it.


          <CFPOP SERVER="#session.mail_server#" USERNAME=#session.UserName# PASSWORD=#session.pwd# ACTION= "GETALL" NAME="all_mails">


             <cfset ldate = ArrayNew(1)>     <!---  for local date/time --->

             <!--- populate the array with local time --->

              <cfloop query="all_mails">

                   <cfset l_date=ParseDateTime(date,"pop")> <!--- CF9 --->

                   <cfset l_date=DateConvert("utc2local", l_date)>

                   <cfset ldate[#currentrow#] = l_date>



               <!--- add a column in the query with a new date field :  l_date  and populate it with the ldate array values --->

             <cfset temp = QueryAddColumn(all_mails, "l_date", "date", ldate)>


             <!--- reorder the query on the new date/time --->

             <cfquery name="GetHeaders_o" dbtype="query"  maxrows="#end_row#">

                   select * from all_mails

                   order by [l_date] desc



          This does work fine for me.

          • 2. Re: CFpop and Time Zone
            plarts Level 1

            Some return on this method :


            The method is good, working, but 2 remarks :


            1 - It would be better that CFPOP could do it itself a sort on dates, because this method takes more time.

                      - a fist loop to rewrite dates in new field : ldate (on all mails)

                      - a second query to reorder (query of query)

            2 - It Happend to me that I get mails without "from" value or "date" value (empty).

                      Then it needs to be verified, and deleted these bad mails during listing.


            Any other remarks or suggestion are welcome.


            • 3. Re: CFpop and Time Zone

              This worked beautifully!  Thank you!!!