Skip to main content
P
plarts
Inspiring
November 20, 2011
Question

CFpop and Time Zone

  • November 20, 2011
  • 1 reply
  • 1209 views

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.

    This topic has been closed for replies.

    1 reply

    P
    plartsAuthor
    Inspiring
    November 20, 2011

    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>

       </cfloop>

         <!--- 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

       </cfquery>

    This does work fine for me.

      P
      plartsAuthor
      Inspiring
      November 26, 2011

      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.

      Thanks.

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices