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

[Newsgroup_User]

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.

[chilliroom]

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

[Newsgroup_User]

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

[chilliroom]

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.

[Newsgroup_User]

chilliroom wrote:
> newDates=dateFormat(dateAdd("s",applicants.posted,unixEpoch));

you missed, newDates =

[chilliroom]

I must be missing something here....where have I missed "newDates=" from?

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

[Newsgroup_User]

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

[chilliroom]

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>

[Newsgroup_User · Accepted solution]

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

[chilliroom]

Thanking you sir, help muchly appreciated.