Converting unix timestamp to #now()#

[BreakawayPaul]

I'm in the process of converting my personal website from PHP to CF9 (I wanted CF10, but apparently CF10/Linux is hard to come by), and when importing my existing blog posts into the new database, I've wound up with unexpected date stamps for the posts.

A post from November of last year has turned into August of some 5-digit year.  I'm assuming this is a unix time thing, and since I'll be using #now()# for future posts, I'll need to convert the existing posts to use the new format.

I'm thinking I can just do an update query, but I have no idea how to turn the existing timestamps into something that DateFormat() will be able to format correctly.  Any advice?

[Adam Cameron.]

The the data is stored in the DB as a date/time, it should come back to CF as a date/time. You shouldn't need to do anything.

Given it's not coming back as you'd expect, I am guessing the data in the DB is not being stored as a date/time data, and this is what your problem is (and, accordingly, where you need to address it).

So this is perhaps a DB question more than a PHP or a CF question. Which DB are you using?

It sounds to me like you're storing the date/timedata in the DB as an offset from the Unix epoch, which is... err... "less than ideal", so it's a matter of converting that to proper date/time data. So what are you storing: a number of seconds? To convert that to a date, you need to use whatever your DB's dateAdd() equivalent is, and add that number to 1970-0-01 (that's the Unix epoch date, innit?).

Rule of thumb: don't store date/time data as anything other than date/time data.

--

Adam

[Tim Cunningham]

Can you give us an example of how the date is currently being stored in your database? ColdFusion not being a strongly typed language does some funky things with dates.

[BreakawayPaul]

I'll post an example timestamp tonight when I get home.  I was going to remote in, but my telecommuting wife has fiddled with the router, so now I can't get to my machine from here.

The old blog was an Invision Power Board blog that I exported to my website (the forum and my website are on the same machine).  The forum is now gone, so I've exported my blog posts to an SQL file, then imported them into my local database (MySQL).  I wrapped the date stamps with #DateFormat(datestamp,"mmmm dd, yyyy")# and got something like August 18, 32653.

[Tim Cunningham]

August 18, 32653 by then we will all be one giant hive mind anyway!

K. I will wait for a sample date, I have a feeling there is probably some SQL CASTing you can do on the date to make ColdFusion like it better.

[BreakawayPaul]

Ok, here's the datestamp right out of the database:

1323041520

Here's what I get when I dateformat() it:

August 23, 3624264

[12Robots · Accepted solution]

Try this function from CFLIB: http://www.cflib.org/udf/EpochTimeToDate

Jason

[BreakawayPaul]

Thanks.  This along with the DateFormat() gives me:

December 04, 2011

Which is probably close to the correct date.  But more importantly, I think without the DateFormat() it gives me something I can plug back into the database to get a consistent date with the new stuff I'll be entering.

Thanks!

[Adam Cameron.]

No, you're not getting it. DateFormat() is just for "prettying-up" a date object for human consumption (like on the screen).

epochTimeToDate() returns a date object, which is what you want to store in the DB. Like I said before: story date/time data as date time data. Don't store it as strings or numbers or anything else. This is what got you into this problem in the first place.

To be honest, pulling the data back to CF and converting it and pushing it back to the DB is the wrong approach here (sorry Jason), because you'll still be putting the wrong data into a wrong-type column. Just add a date/time column to the table, and use the logic that's in epochTimeToDate() in your DB to update the new column with the correct value. Something like:

UPDATE table

SET newDateTimeColumn = YOUR_DB_DATE_ADD_FUNCTION('1970-01-01', oldColumn)

Then drop oldColumn.

But whichever way you do it, do NOT be putting strings back into your DB.

--

Adam

[BreakawayPaul]

Adam Cameron. wrote:

UPDATE table

SET newDateTimeColumn = YOUR_DB_DATE_ADD_FUNCTION('1970-01-01', oldColumn)

Then drop oldColumn.

But whichever way you do it, do NOT be putting strings back into your DB.

This is exactly what I did, and it works perfectly now.  I didn't mean that I was plugging in the DateFormat()ed date, I just wanted a consistent datestamp that I could format with DateFormat() when the page is rendered, and that's now what I have.

Anyway, I couldn't plug the new date back into the old column, because the old column was type INT and that wouldn't have worked.  But the new column with type DATE works, and I'm off to the next step of my website conversion.

[Adam Cameron.]

Cool! Good stuff.

--

Adam