Solved

Coldfusion : Unix Epoch Time

Posted on 2016-11-29
4
10 Views
Last Modified: 2016-11-30
Hello,
I have a Coldfusion 8 program that receives a character string that represents a UNIX "epoch date" (milliseconds from 1/1/1970).  It is from a JSON file input.  An example from the JSON file::

{"created" : 1417392180000,
		"endReason" : "expired",
		"ended" : 1417737780003}

Open in new window


I want to update a sqlserver datetime column with this date/time

Update mytable
  Set createdate = 'epochdate'
    where mykey = 32

How do I format 'epochdate' for the SQL to update the datetime column?

Thanks in advance,
hefterr
0
Comment
Question by:hefterr
  • 2
  • 2
4 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 41906527
Assuming you've deserialized the json input:

     <cfset json = deserializeJSON(theString)>

Something like:
     <cfset date = dateAdd("s", json.created/1000, "1970-01-01")>

      <cfquery ...>
       UPDATE mytable
       SET         createdate = <cfqueryparam value="#json.created/1000#" cfsqltype="cf_sql_timestamp">
       WHERE ....
      </cfquery>

Open in new window

.. or in sql server
      <cfquery ...>
       UPDATE mytable
       SET createdate = dateAdd(s,  <cfqueryparam value="#json.created/1000#", '1970-01-01')
        WHERE ....
      </cfquery>

Open in new window

0
 
LVL 1

Author Comment

by:hefterr
ID: 41906580
Hi _agx_,
I haven't used Coldfusion for a few years.  You used to anwser many questions for me :)  Thanks!!!

Just to clarify a few things.

1)  Why do you divide the JSON time (which is in in milliseconds) by 1000.  Dateadd has a parameter "l" for milliseconds.  Is it because SQL Server does not?  How does Dateadd handle the fraction.  Say for json.created = 1436415972428?

2)  In your first  <cfset date = dateAdd("s", json.created/1000, "1970-01-01")>  you don't use the variable date anywhere.  What is the purpose of this statement?

Thanks,
hefterr
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 41906623
Yes, long time no see :)

>> 1)  Why do you divide the JSON time (which is in in milliseconds) by 1000.  Dateadd has a parameter "l" for milliseconds.  
>> Is it because SQL Server does not?  
SQL Server supports it too. It is "ms" (milliseconds").  Unfortunately both of the functions require the interval part to be an INT. Your raw values are too big to fit inside an INT. So it'll crash unless you convert it to seconds first, ie divide by 1000.

>> How does Dateadd handle the fraction.  Say for json.created = 1436415972428?
It gets rounded to the nearest 1000th, ie

      1436415972.43   <== rounded to 1436415972
      Thursday, July 9, 2015 5:26:12 AM  

>> you don't use the variable date anywhere.  
Sorry, my bad. Typo. The "date" variable should've been used in the cfqueryparam:

          createdate = <cfqueryparam value="#date#" cfsqltype="cf_sql_timestamp">
0
 
LVL 1

Author Closing Comment

by:hefterr
ID: 41907344
Thanks again for your help.  I will have a question on parsing a more complex JSON file later on.  I just need to study a bit and prepare a simple case.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now