• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Coldfusion : Unix Epoch Time

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
hefterr
Asked:
hefterr
  • 2
  • 2
2 Solutions
 
_agx_Commented:
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
 
hefterrAuthor Commented:
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
 
_agx_Commented:
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
 
hefterrAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now