diff --git a/servers/php/postgresql/gpstracker-09-14-14.sql b/servers/php/postgresql/gpstracker-09-14-14.sql index e4322f6..b7442f7 100644 --- a/servers/php/postgresql/gpstracker-09-14-14.sql +++ b/servers/php/postgresql/gpstracker-09-14-14.sql @@ -23,7 +23,7 @@ speed integer NOT NULL DEFAULT '0', direction integer NOT NULL DEFAULT '0', distance double precision NOT NULL DEFAULT '0.0', - gpsTime timestamp NOT NULL DEFAULT '0001-01-01 00:00:00', + gpsTime timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', locationMethod varchar(50) NOT NULL DEFAULT '', accuracy integer NOT NULL DEFAULT '0', extraInfo varchar(255) NOT NULL DEFAULT '', @@ -43,7 +43,7 @@ (1,'2007-01-03 19:37:00',47.627327,-122.325691,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:37:00','na',137,'na','gpsTracker'), (2,'2007-01-03 19:38:00',47.607258,-122.330077,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:38:00','na',137,'na','gpsTracker'), (3,'2007-01-03 19:39:00',47.601703,-122.324670,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:39:00','na',137,'na','gpsTracker'), -(4,'0001-01-01 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'), +(4,'0000-00-00 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'), (5,'2007-01-03 19:41:00',47.601397,-122.190353,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:41:00','na',137,'na','gpsTracker'), (6,'2007-01-03 19:42:00',47.610020,-122.190697,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:42:00','na',137,'na','gpsTracker'), (7,'2007-01-03 19:43:00',47.636631,-122.214558,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:43:00','na',137,'na','gpsTracker'), @@ -60,7 +60,7 @@ concat('{ "latitude":"', gpslocations.latitude::character varying, '", "longitude":"', gpslocations.longitude::character varying, '", "speed":"', gpslocations.speed::character varying, '", "direction":"', gpslocations.direction::character varying, '", "distance":"', gpslocations.distance::character varying, '", "locationMethod":"', gpslocations.locationmethod, '", "gpsTime":"', to_char(gpslocations.gpstime, 'Mon dd YYYY HH12:MIAM'::text), '", "userName":"', gpslocations.username, '", "phoneNumber":"', gpslocations.phonenumber, '", "sessionID":"', gpslocations.sessionid, '", "accuracy":"', gpslocations.accuracy::character varying, '", "extraInfo":"', gpslocations.extrainfo, '" }') AS json FROM ( SELECT max(gpslocations_1.gpslocationid) AS id FROM gpslocations gpslocations_1 - WHERE gpslocations_1.sessionid::text <> '0'::text AND char_length(gpslocations_1.sessionid::text) <> 0 AND gpslocations_1.gpstime <> '0001-01-01 00:00:00'::timestamp without time zone + WHERE gpslocations_1.sessionid::text <> '0'::text AND char_length(gpslocations_1.sessionid::text) <> 0 AND gpslocations_1.gpstime <> '0000-00-00 00:00:00'::timestamp without time zone GROUP BY gpslocations_1.sessionid) maxid JOIN gpslocations ON gpslocations.gpslocationid = maxid.id ORDER BY gpslocations.gpstime; @@ -75,7 +75,7 @@ lastupdate, CONCAT('{ "latitude":"', CAST(latitude AS VARCHAR),'", "longitude":"', CAST(longitude AS VARCHAR), '", "speed":"', CAST(speed AS VARCHAR), '", "direction":"', CAST(direction AS VARCHAR), '", "distance":"', CAST(distance AS VARCHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', to_char(gpsTime, 'Mon dd YYYY HH12:MIAM'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS VARCHAR), '", "accuracy":"', CAST(accuracy AS VARCHAR), '", "extraInfo":"', extraInfo, '" }') json FROM gpslocations - WHERE gpstime != '0001-01-01 00:00:00' + WHERE gpstime != '0000-00-00 00:00:00' ORDER BY lastupdate; ; diff --git a/servers/php/sqlite/gpstracker-09-14-14.sql b/servers/php/sqlite/gpstracker-09-14-14.sql index 866903f..31a5866 100644 --- a/servers/php/sqlite/gpstracker-09-14-14.sql +++ b/servers/php/sqlite/gpstracker-09-14-14.sql @@ -21,7 +21,7 @@ speed integer NOT NULL DEFAULT '0', direction integer NOT NULL DEFAULT '0', distance double precision NOT NULL DEFAULT '0.0', - gpsTime timestamp NOT NULL DEFAULT '0001-01-01 00:00:00', + gpsTime timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', locationMethod varchar(50) NOT NULL DEFAULT '', accuracy integer NOT NULL DEFAULT '0', extraInfo varchar(255) NOT NULL DEFAULT '', @@ -39,7 +39,7 @@ (1,'2007-01-03 19:37:00',47.627327,-122.325691,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:37:00','na',137,'na','gpsTracker'), (2,'2007-01-03 19:38:00',47.607258,-122.330077,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:38:00','na',137,'na','gpsTracker'), (3,'2007-01-03 19:39:00',47.601703,-122.324670,'gpsTracker3','gpsTracker3','8BA21D90-3F90-407F-BAAE-800B04B1F5EB',0,0,0.0,'2007-01-03 19:39:00','na',137,'na','gpsTracker'), -(4,'0001-01-01 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'), +(4,'0000-00-00 00:00:00',47.593757,-122.195074,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:40:00','na',137,'na','gpsTracker'), (5,'2007-01-03 19:41:00',47.601397,-122.190353,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:41:00','na',137,'na','gpsTracker'), (6,'2007-01-03 19:42:00',47.610020,-122.190697,'gpsTracker2','gpsTracker2','8BA21D90-3F90-407F-BAAE-800B04B1F5EC',0,0,0.0,'2007-01-03 19:42:00','na',137,'na','gpsTracker'), (7,'2007-01-03 19:43:00',47.636631,-122.214558,'gpsTracker1','gpsTracker1','8BA21D90-3F90-407F-BAAE-800B04B1F5ED',0,0,0.0,'2007-01-03 19:43:00','na',137,'na','gpsTracker'), @@ -70,7 +70,7 @@ FROM gpslocations gpslocations_1 WHERE gpslocations_1.sessionid != '0' AND length(gpslocations_1.sessionid) != 0 AND - gpslocations_1.gpstime != '0001-01-01 00:00:00' + gpslocations_1.gpstime != '0000-00-00 00:00:00' GROUP BY gpslocations_1.sessionid) maxid JOIN gpslocations ON gpslocations.gpslocationid = maxid.id ORDER BY gpslocations.gpstime; @@ -97,7 +97,7 @@ '", "accuracy":"' || CAST(accuracy AS VARCHAR) || '", "extraInfo":"' || extraInfo || '" }' json FROM gpslocations - WHERE gpstime != '0001-01-01 00:00:00' + WHERE gpstime != '0000-00-00 00:00:00' ORDER BY lastupdate; ; diff --git a/servers/php/updatelocation.php b/servers/php/updatelocation.php index afe6b7d..b8d193c 100644 --- a/servers/php/updatelocation.php +++ b/servers/php/updatelocation.php @@ -9,7 +9,7 @@ $direction = isset($_GET['direction']) ? $_GET['direction'] : 0; $distance = isset($_GET['distance']) ? $_GET['distance'] : '0'; $distance = (float)str_replace(",", ".", $distance); - $date = isset($_GET['date']) ? $_GET['date'] : '0001-01-01 00:00:00'; + $date = isset($_GET['date']) ? $_GET['date'] : '0000-00-00 00:00:00'; $date = urldecode($date); $locationmethod = isset($_GET['locationmethod']) ? $_GET['locationmethod'] : ''; $locationmethod = urldecode($locationmethod);