Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Coldfusion : Unix Epoch Time

Posted on 2016-11-29
4
Medium Priority
?
137 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 53

Accepted Solution

by:
_agx_ earned 2000 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 53

Assisted Solution

by:_agx_
_agx_ earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline
Suggested Courses

580 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