Grid datetime in json

[lenomadParticipant]

Hi guys,

I have an annoying problem with a grid where unix epoch timestamps converted to ticks in 2 separate json source files displays fine in one grid but not another on the same page (shows no data to display). If I swap the source url’s the problem follows the file, if I place the json data as local data (copy paste straight from the browser) they both work fine. If I leave the date as unix epoch, i.e. don’t multiply by 1000 in php, they both work fine except the log data displays but is obviously wrong as the cell formatting shows the year as 1970.

The project:
I have an Arduino reading an eTape water sensor in a pit and controlling pumps to ensure the pit doesn’t overflow. Every minute the Arduino sends a serial message to a python script running on a Raspberry Pi that stores the data in a SQLite3 database for use with a web server. There are 2 tables; Water Level samples every minute (Level) and an event log (Log). Each entry is timestamped (Unix Epoch).

Level:
sTimeStamp Numeric
WaterLevel Numeric

Log:
eTimeStamp Numeric
EventText Text

I am using php to extract data from the database as json for two jqxgrids, one for the last 500 log entries and a second for the last 24 hours of water level samples. It extracts the data and converts the timestamp to java ticks (milliseconds) by multiplying by 1000.

The php code:

level-1440.php

<?php
try {
	$db = new PDO("sqlite:waterlevel.db");
    }
catch(PDOException $e) {
    die( "Sorry! There seems to be a problem connecting to the database.");
    }
// Extract the last day's water level data, starting 1 minute prior to current time
$startTime = time() - (1441 * 60);
// Get data, limited to 1440 samples in reverse order (latest = first)
$result = $db->query('SELECT * FROM level WHERE sTimeStamp >= '.$startTime.' ORDER BY sTimeStamp DESC LIMIT 1440')->fetchAll(PDO::FETCH_ASSOC);
//Now convert the Unix Epoch timestamp to a javascript tick format
foreach ($result as &$row){
	//convert timestamp to javascript tick in milliseconds
	$row['sTimeStamp'] *= 1000;
}
//display in json format, making sure numeric entries are not encoded as strings
echo json_encode($result, JSON_NUMERIC_CHECK);
$db->close();
?>

log-500.php

<?php
try {
	$db = new PDO("sqlite:waterlevel.db");
    }
catch(PDOException $e) {
    die( "Sorry! There seems to be a problem connecting to the database.");
    }
// Extract the last day's water level data, starting 1 minute prior to current time
$startTime = time() - (1441 * 60);
// Get data, limited to 1440 samples in reverse order (latest = first)
$result = $db->query('SELECT * FROM log ORDER BY eTimeStamp DESC LIMIT 500')->fetchAll(PDO::FETCH_ASSOC);
//Now convert the Unix Epoch timestamp to a javascript tick format
foreach ($result as &$row){
	//convert timestamp to javascript tick in milliseconds
	$row['eTimeStamp'] *= 1000;
}
//display in json format, making sure numeric entries are not encoded as strings
echo json_encode($result, JSON_NUMERIC_CHECK);
$db->close();
?>

This is producing the following output (extract):
level-1440:

[{"sTimeStamp":1417434839000,"WaterLevel":25.25},{"sTimeStamp":1417434779000,"WaterLevel":25.25},
. . . . . . 
"sTimeStamp":1417434719000,"WaterLevel":25.25},{"sTimeStamp":1417434659000,"WaterLevel":25.25}]

and

log-500.php:

[{"eTimeStamp":1417002384000,"EventText":"Program Started"},{"eTimeStamp":1416980486000,"EventText":"Program Started"},
. . . . . .
{"eTimeStamp":1416978539000,"EventText":"Pump = 1"},{"eTimeStamp":1416978419000,"EventText":"Pump = 0"}]

I have created a web page with 3 splitters (main horizontal, vertical in both top & bottom divs) to give me 4 semi independent display areas where the grids are on the lower splits and the upper will be used for a gauge & a line graph which I haven’t implemented yet. Both grids are similar in that there are only 2 colums, each having a timestamp while one grid also has a numeric water level and the other event text. I am currently developing the page from my desktop and it will run on the Pi when finished, hence the hard coded ip address.

The code for the page:

<!DOCTYPE html>
<html lang="en">
<head>
	<title id='Description'>RaspUino Water Level. </title>
	<link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
	<script type="text/javascript" src="../../scripts/jquery-1.11.1.min.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxsplitter.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxdraw.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxchart.core.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxchart.rangeselector.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>    
	<script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
	<script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script> 
	<script type="text/javascript" src="../../jqwidgets/jqxgrid.columnsresize.js"></script>     
	<script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
	<!-- Javascript -->
	<script type="text/javascript">
	$(document).ready(function () {
		// *********** Splitters *************
		$('#horizSplitter').jqxSplitter({ width: '100%', height: '85%', orientation: 'horizontal', panels: [{ size: '50%', min: 250 }, { size: '50%', min: 250}] });
		$('#lowerSplitter').jqxSplitter({ height: '100%', orientation: 'vertical', panels: [{ size: '60%'}, { size: '40%'}] });
		$('#upperSplitter').jqxSplitter({ height: '100%', orientation: 'vertical', panels: [{ size: '30%'}, { size: '70%'}] });
		
		
		// *********** Event Log *************
		var source =
		{
		    datatype: "json",
		    datafields: [
		        { name: 'eTimeStamp', type: 'date' },
		        { name: 'EventText', type: 'string' }
		    ],
		    url: "http://192.168.1.17/log-500.php"
		};
		var dataAdapter = new $.jqx.dataAdapter(source, 
		    {
		        formatData: function (data) {
		            $.extend(data, {
		                featureClass: "P",
		                style: "full",
		                maxRows: 50,
		                username: "jqwidgets"
		            });
		            return data;
		        }
		    }
		);
		$("#jqxgrid").jqxGrid(
		{
		    width: '90%',
		    height: '85%',
		    source: dataAdapter,
		    columnsresize: true,
		    columns: [
		        { text: 'Time', datafield: 'eTimeStamp', width: '40%', cellsformat: 'ddd dd.MM.yyyy hh:mm:ss tt' },
		        { text: 'Event Details', datafield: 'EventText', width: '60%' }
		    ]
		});           
		
		
		// *********** Last 24 Hours *************
		var source2 =
		{
		    datatype: "json",
		    datafields: [
		        { name: 'sTimeStamp', type: 'date' },
		        { name: 'WaterLevel', type: 'string' }
		    ],
		    url: "http://192.168.1.17/level-1440.php"
		};
		var dataAdapter2 = new $.jqx.dataAdapter(source2, 
		    {
		        formatData: function (data) {
		            $.extend(data, {
		                featureClass: "P",
		                style: "full",
		                maxRows: 50,
		                username: "jqwidgets"
		            });
		            return data;
		        }
		    }
		);
		$("#jqxgrid2").jqxGrid(
		{
		    width: '90%',
		    height: '85%',
		    source: dataAdapter2,
		    columnsresize: true,
		    columns: [
		        { text: 'Time', datafield: 'sTimeStamp', width: '60%', cellsformat: 'ddd dd.MM.yyyy hh:mm:ss tt' },
		        { text: 'Water Level', datafield: 'WaterLevel', width: '40%', cellsformat: 'F2' }
		    ]
		});            
	});
    </script>
     <!-- CSS -->
    <style type="text/css">
    html, body 
    {
        height: 100%;
        width: 100%;
        margin: 0px;
        padding: 0px;
        overflow: hidden;
        font-family: Verdana;
    }
    </style>
</head>
<body class='default'> <!--  -->
	<div align = "center" style="height:80px"> <!-- Header div -->
		<H1>Pit Water Level</H1>
	</div>
	<div id='horizSplitter'> <!-- Main Horizontal Splitter gives an upper & lower div -->
		<div>	
			<div id='upperSplitter'> <!-- Add a splitter to divide lower into left & right divs -->
				<div align = "center"> <!-- left -->
					<h2> Current Level</h2>
				</div>
				<div align = "center"> <!-- right -->
					<h2>Graph</h2>
				</div>
			</div>
		</div>
		<div align="center"> <!-- Lower div -->
			<div id='lowerSplitter'> <!-- Add a splitter to divide lower into left & right divs -->
				<div style="font-size: 13px;"> <!-- left -->
					<h2> Event Log</h2>
					<div id="jqxgrid">
					</div>
				</div>
				<div style="font-size: 13px;"> <!-- right -->
					<h2> Last 24 Hours</h2>
					<div id="jqxgrid2">
					</div>
				</div>
		 	</div>
			<div> </div><!-- Empty div for some reason ????? -->
		</div>
	</div>
	<div align = "center" style="height:80px"> <!-- Footer div -->
		<H3>RaspUino<br></H3>
	</div>	 	
</body>
</html>

Everything so far is working like a charm, except for this frustrating date problem.

Grateful for any assistance.

[Peter StoevKeymaster]

hi lenomad,

May be it’s necessary for your case to specify the format of the “date” field. Example: Example: { name: ‘SubmitDate’, type: ‘date’, format: “yyyy-MM-ddTHH:mm:ss-HH:mm” }

By doing that, you’ll ensure that the Grid will use specific format for Parsing the Date which comes from the Server to JavaScript Date object.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[lenomadParticipant]

Hi Peter,

The json data is supplying the timestamps as a javascript date not as formatted text, how would I specify a format string for that?

But the confusion is that the same data format is provided to the second grid and it is parsed perfectly, and if I place it as local json data it parses that perfectly as well.

If I may ask, have I replicated the 2 grids correctly making sure not to duplicate variables, etc?

Thanks

[Peter StoevKeymaster]

It seems Ok, however I don’t think that the following is necessary in your code:

   formatData: function (data) {
		            $.extend(data, {
		                featureClass: "P",
		                style: "full",
		                maxRows: 50,
		                username: "jqwidgets"
		            });
		            return data;
		        }

The above is a sample from our documentation for specifying custom params with an Ajax request and is specific for the GeoNames Web Service.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[lenomadParticipant]

OK, I’ve removed it. I think perhaps the easiest solution is to convert the unix time to a formatted string in the php code and add a format string to the datafields definition as you suggested.

Thank you.

[Author]

Posts