Solved

Coldfusion : Unix Epoch Time

Posted on 2016-11-29
4
46 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion remove square brackets from string 4 144
dynamic form fields and inserting into database 3 47
Coldfusion RegEx 8 79
Lucee & <cftransaction.... />  WITAF? 10 30
The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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