Skip to main content
BreakawayPaul
BreakawayPaul
Inspiring
January 3, 2013
Answered

Converting unix timestamp to #now()#

  • January 3, 2013
  • 2 replies
  • 5908 views

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?

    This topic has been closed for replies.
    Correct answer 12Robots

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

    1323041520

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

    August 23, 3624264


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

    Jason

    2 replies

    Tim Cunningham
    Tim Cunningham
    Participating Frequently
    January 3, 2013

    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
    BreakawayPaulAuthor
    Inspiring
    January 3, 2013

    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
    Tim Cunningham
    Participating Frequently
    January 3, 2013

    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.

    A
    Adam Cameron.
    Inspiring
    January 3, 2013

    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

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