10 Replies Latest reply on Jan 3, 2013 4:25 PM by Adam Cameron.

    Converting unix timestamp to #now()#

    BreakawayPaul Level 2

      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?

        • 1. Re: Converting unix timestamp to #now()#
          Adam Cameron. Level 5

          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

          • 2. Re: Converting unix timestamp to #now()#
            Tim Cunningham-ACP Level 2

            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.

            • 3. Re: Converting unix timestamp to #now()#
              BreakawayPaul Level 2

              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.

              • 4. Re: Converting unix timestamp to #now()#
                Tim Cunningham-ACP Level 2

                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.

                • 5. Re: Converting unix timestamp to #now()#
                  BreakawayPaul Level 2

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

                   

                  1323041520

                   

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

                   

                  August 23, 3624264

                  • 7. Re: Converting unix timestamp to #now()#
                    BreakawayPaul Level 2

                    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!

                    • 8. Re: Converting unix timestamp to #now()#
                      Adam Cameron. Level 5

                      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

                      • 9. Re: Converting unix timestamp to #now()#
                        BreakawayPaul Level 2

                        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.

                        • 10. Re: Converting unix timestamp to #now()#
                          Adam Cameron. Level 5

                          Cool! Good stuff.

                           

                          --

                          Adam