10 Replies Latest reply on Mar 12, 2007 1:48 AM by chilliroom

    Unix timestamp in cfgrid

    chilliroom
      Sometime ago I produced a php and SQL jobsite. Users apply for jobs and the date they apply is converted into a unix timestamp and inserted into the SQL as an integer.

      I am now using coldfusion increasingly more, and am wanting to display the results above using a cfgrid.

      Although I have found similar posts in this forum I still have two oustanding queries:

      1) How do I catch the date for each row before the cfgrid displays it

      2) How to convert unix timestamp using coldfusion

        • 1. Re: Unix timestamp in cfgrid
          Level 7
          chilliroom wrote:
          > 1) How do I catch the date for each row before the cfgrid displays it

          format it via the db is probably the cheapest.

          > 2) How to convert unix timestamp using coldfusion

          if you don't mind maybe losing some accuracy (use seconds dateparts but
          depending on how far back your dates go this might overflow), use dateAdd w/the
          unix epoch:

          unixEpoch=createDate(1970,1,1);
          newDate=datAdd("s",yourUnixEpochOffsetData,unixEpoch);

          note that this might give you some trouble as cf will see newDate as a cf server
          datetime & apply any timezone rules to it.
          • 2. Re: Unix timestamp in cfgrid
            chilliroom Level 1
            quote:

            format it via the db is probably the cheapest.

            I am still unsure how to catch the date for each row before the cfgrd grabs it.

            How can I extract this value to then feed into "yourUnixEpochOffsetData".

            unixEpoch=createDate(1970,1,1);
            newDate=datAdd("s",yourUnixEpochOffsetData,unixEpoch);
            • 3. Re: Unix timestamp in cfgrid
              Level 7
              chilliroom wrote:
              >
              quote:

              format it via the db is probably the cheapest.

              > I am still unsure how to catch the date for each row before the cfgrd grabs it.

              what db? does it have this functionality? for sql server something like should
              work (but again watch for overflow):

              SELECT dateAdd(second,yourUnixEpochOffsetDataColumn,'1-jan-1970') as aDate,....
              FROM yourTable

              > How can I extract this value to then feed into "yourUnixEpochOffsetData".

              if your db can't handle this, here's one way using cf (but the db is cheaper).

              do your normal cfquery then:

              - convert the unix epoch offsets to datetimes:

              unixEpoch=createDate(1970,1,1);
              newDates=arrayNew(1);
              for (i=1; i LTE yourRegularcfQuery.recordCount;i=i+1) {
              newDates =dateAdd("s",yourRegularcfQuery.yourUnixEpochOffsetDataColumn,unixEpoch);
              }

              depending on your needs, you might also want to dateFormat the results of the
              dataAdd:

              newDates =dateFormat(dateAdd("s",yourRegularcfQuery.yourUnixEpochOffsetDataColumn,unixEpoch));

              - add a column to your cfquery & fill with those converted dates,

              queryAddColumn(yourRegularcfQuery,"aDate","date",newDates);
              • 4. Re: Unix timestamp in cfgrid
                chilliroom Level 1
                Thanks again Paul.
                I have tried the following, managing to convert the unix timestamp but not reinsert into the cfgrid, receiving the following error:

                Object of type class java.lang.String cannot be used as an array

                <cfscript>
                unixEpoch=createDate(1970,1,1);
                newDates=arrayNew(1);
                for (i=1; i LTE applicants.recordCount;i=i+1)
                {
                newDates=dateFormat(dateAdd("s",applicants.posted,unixEpoch));
                }
                queryAddColumn(applicants,"aDate",newDates);
                </cfscript>

                To answer your previous question, it is an SQL database.
                • 5. Re: Unix timestamp in cfgrid
                  Level 7
                  chilliroom wrote:
                  > newDates=dateFormat(dateAdd("s",applicants.posted,unixEpoch));

                  you missed, newDates =
                  • 6. Re: Unix timestamp in cfgrid
                    chilliroom Level 1
                    I must be missing something here....where have I missed "newDates=" from?

                    It is already included here:
                    newDates=dateFormat(dateAdd("s",applicants.posted,unixEpoch));

                    • 7. Re: Unix timestamp in cfgrid
                      Level 7
                      chilliroom wrote:
                      > I must be missing something here....where have I missed "newDates=" from?
                      >
                      > It is already included here:
                      > newDates=dateFormat(dateAdd("s",applicants.posted,unixEpoch));

                      i think the news group is stripping out the square brackets for the newDates.
                      just do an arrayAppend instead:
                      arrayAppend(newDates,dateFormat(dateAdd("s",applicants.posted,unixEpoch)));
                      • 8. Re: Unix timestamp in cfgrid
                        chilliroom Level 1
                        Brilliant Paul, thats nearly solved it. I am able to feed the results back into the grid to display.
                        However, the date column is now reading entirely the same value, which I have calculated to be the last record's date.
                        I cant see why the array is settng this in one hit ?

                        <cfscript>
                        unixEpoch=createDate(1970,1,1);
                        newDates=arrayNew(1);
                        for (i=1; i LTE applicants.recordCount;i=i+1)
                        { arrayAppend(newDates,dateFormat(dateAdd("s",applicants.posted,unixEpoch))); }
                        queryAddColumn(applicants,"aDate",newDates);
                        </cfscript>
                        • 9. Re: Unix timestamp in cfgrid
                          Level 7
                          chilliroom wrote:
                          > However, the date column is now reading entirely the same value, which I have
                          > calculated to be the last record's date.

                          because you're not advancing thru your query. i guess the newsserver stripped
                          out the square brackets for applicants.posted [ i ]. lets see if the cf tags get
                          thru:

                          <cfset unixEpoch=createDate(1970,1,1)>
                          <cfset newDates=arrayNew(1)>
                          <cfoutput query="applicants">
                          <cfset arrayAppend(newDates,dateFormat(dateAdd("s",posted,unixEpoch)))>
                          </cfoutput>
                          <cfset queryAddColumn(applicants,"aDate",newDates)>
                          • 10. Re: Unix timestamp in cfgrid
                            chilliroom Level 1
                            Thanking you sir, help muchly appreciated.