Solved

What is the correct way to send a datetime value from php in JSON format?

Posted on 2014-10-10
11
2,862 Views
Last Modified: 2014-10-10
Hi.
aaaa-mm-dd hh:mi:ss.mmm
I have a datetime value that is get from sql-server on a php variable. This is a string like: 2014-09-23 18:45:23.534

What is the correct way to convert it to json as a datetime?

Thanks.
0
Comment
Question by:gplana
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 300 total points
ID: 40372609
Here is an example of what I think you are looking for. Javascript is fairly flexible on dates, just not quite as flexible as PHP.

<?php

error_reporting(E_ALL); ini_set('display_errors',1);



$dt = "2014-09-23 18:45:23.534";

$testDate = date("c", strtotime($dt) );
$jsonDate = json_encode( $testDate );


echo "In PHP the date is $testDate";
echo "<br/>";
echo "In PHP the JSON date is $jsonDate";

?>


<script type='text/javascript'>

     var testDate = new Date("<?php echo $testDate; ?>");
     alert( testDate );

     var jsonDate = new Date(<?php echo $jsonDate; ?>);
     alert( jsonDate );


</script>

Open in new window


This produced the following source code, but running it is the best way to see the outcome

In PHP the date is 2014-09-23T18:45:23+01:00<br/>In PHP the JSON date is "2014-09-23T18:45:23+01:00"

<script type='text/javascript'>

     var testDate = new Date("2014-09-23T18:45:23+01:00");
     alert( testDate );

     var jsonDate = new Date("2014-09-23T18:45:23+01:00");
     alert( jsonDate );

</script>

Open in new window

0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 200 total points
ID: 40372725
All the background information:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

PHP date('c') will give you the ISO-8601 format date, which is the correct format for information interchange.  PHP will almost certainly lose the fractions of seconds; don't worry about this.  As a practical matter it has little effect on well-designed applications.

There is one little hiccup in JavaScript dates, and that is the way months are numbered.  When we think of January, we think of "month number 1" but in JavaScript, the months are numbered from zero to eleven.  So January is month zero, February is month one, etc.
0
 
LVL 7

Expert Comment

by:Gauthier
ID: 40372735
@Beverley Portlock
The dreaded killing unix epoch conversion... It needs to be more resilient than that!
Unfortunately, the more appropriate PHP DateTime() will add quite a bit of unwanted items to the JSON.
and won't accept your string anyway...

Since you have an unusable string anyway, you could simply kludge it like this
For UTC:
$jsDateString = str_replace(' ', 'T', $sqlDateString) . 'Z';
Or if your time refer to a timezone:
$jsDateString = str_replace(' ', 'T', $sqlDateString) . '+01:00';

If you need date processing on the PHP side too, you later use DateTime(), but consider adding your own conversion to json string since PHP doesn't serialize the DateTime() in a usable form by the Javascript.
A json date usable in Javascript need to be DateTime::ISO8601 or preferably DateTime::W3C.
    $sqlDateString = "2014-09-23 18:45:23.534";
    $jsDateString = Replace(' ', 'T', $sqlDateString) . 'Z';
    $appDate = new DateTime($this->jsDateString,  new DateTimeZone("Europe/Amsterdam"));
    $jsonDateString = $appDate->format(DateTime::W3C);

Open in new window

0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40372755
@Gauthier said "The dreaded killing unix epoch conversion... It needs to be more resilient than that!"

Does it really? Surely it depends on the application? If all the dates are short term ones and well short of 2038 then it will work just fine. Not many of us need dates 25 years ahead and if a date is needed that far ahead then other options are available. If it is converting log dates or reporting on stuff that happened recently then I doubt it will ever be an issue.

@Ray said "There is one little hiccup in JavaScript dates, and that is the way months are numbered.  When we think of January, we think of "month number 1" but in JavaScript, the months are numbered from zero to eleven.  So January is month zero, February is month one, etc. "

I had forgotten about that but even so I think that dates in YMD format are OK in Javascript. Jan 1st this year was not 2014-00-01 if you see what I mean.
0
 
LVL 7

Expert Comment

by:Gauthier
ID: 40372790
@Beverley Portlock said
Does it really?

Why would you want to set you up for failure by writing bad code?
Not that the sample code I wrote above is any good mind you:
It ignored the handling of null, undefined date, etc.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40372806
@Gauthier - "Why would you want to set you up for failure by writing bad code?"

It does not have to be "bad" code. It just has to be appropriate for the use to which it is being put. For calculating pension and retirement dates then it is not suitable. If it is converting logfile time stamps then it will work just fine. Horses for courses.
0
 
LVL 7

Expert Comment

by:Gauthier
ID: 40372812
Code need to be reusable, when you are writing an interface between json and a db you cannot make that kind of assumption, writing ad hoc code in this context will bite you badly.
0
 
LVL 15

Author Closing Comment

by:gplana
ID: 40373004
Thank you both. It works perfectly this way.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40373048
Thank you for the points.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40373455
Thanks for the points.  From the date() man page:
The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer). However, before PHP 5.1.0 this range was limited from 01-01-1970 to 19-01-2038 on some systems (e.g. Windows).
I don't know anybody who expects to be using 32-bit systems in 20 years, so I'm going to bet on the value of the timestamp increasing greatly with the 64-bit systems.  And I think my bet is assured.  This from the "intro" page to the PHP date/time extension:
The date and time information is internally stored as a 64-bit number so all conceivably useful dates (including negative years) are supported. The range is from about 292 billion years in the past to the same in the future.
0
 
LVL 15

Author Comment

by:gplana
ID: 40373464
You are both more than welcome. Thanks to you for helping me and explain in that so clear way.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

867 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

15 Experts available now in Live!

Get 1:1 Help Now