Solved

Coldfusion : Unix Epoch Time

Posted on 2016-11-29
4
40 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 our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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