fix gnss timestamp

server/sql/tables.sql

@@ -16,7 +16,7 @@ CREATE TABLE IF NOT EXISTS wifi_scan (
     lp_ttn_end_device_uplinks_id UUID,
     mac VARCHAR(255),
     rssi NUMERIC,
-    scanned_at_utc DATE NOT NULL;
+    scanned_at_utc TIMESTAMP NOT NULL,
     created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
@@ -64,7 +64,7 @@ CREATE TABLE IF NOT EXISTS location (
     gnss_longitude DOUBLE,
     ttn_gw_latitude DOUBLE,
     ttn_gw_longitude DOUBLE,
-    gnss_location_at_utc DATE;
+    gnss_location_at_utc TIMESTAMP,
     created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)

server/src/controller/ttnController.ts

@@ -51,14 +51,14 @@ router.post(
         )?.measurementValue,
       };
 
+      const gnssMetadata = message.uplink_message.decoded_payload?.messages[0].find(
+        (e) => e.type === "Latitude"
+      );
+
       const gnssTimestamp = {
-        timestamp: message.uplink_message.decoded_payload?.messages[0].find(
-          (e) => e.type === "Latitude"
-        )?.timestamp
+        timestamp: gnssMetadata?.timestamp ? new Date(gnssMetadata.timestamp) : undefined
       };
 
-
-
       const wifiScans =
         message.uplink_message.decoded_payload?.messages[0]
           .find((e) => e.type === "Wi-Fi Scan")
@@ -102,13 +102,12 @@ router.post(
           gnss:
             gnnsLocation.latitude && gnnsLocation.longitude
               ? {
-                  latitude: gnnsLocation.latitude,
-                  longitude: gnnsLocation.longitude,
+                latitude: gnnsLocation.latitude,
+                longitude: gnnsLocation.longitude,
 
-                }
+              }
               : undefined,
-
-              gnss_timestamp: new Date(gnssTimestamp.timestamp),
+          gnss_timestamp: gnssTimestamp.timestamp,
         });
       };
       createDatabaseEntries().then();

server/src/models/location.ts

@@ -10,7 +10,7 @@ export class Location extends Model {
   public gnss_longitude!: number;
   public ttn_gw_latitude!: number;
   public ttn_gw_longitude!: number;
-  public gnss_timestamp!: Date;
+  public gnss_location_at_utc!: Date;
   public created_at_utc!: Date;
   public updated_at_utc!: Date;
 }

server/src/models/wifiScan.ts

@@ -6,6 +6,7 @@ export class WifiScan extends Model {
   public wifi_scan_id!: string;
   public mac!: string;
   public rssi!: number;
+  public scanned_at_utc!: Date;
   public created_at_utc!: Date;
   public updated_at_utc!: Date;
 }
@@ -32,6 +33,8 @@ WifiScan.init(
     },
     scanned_at_utc: {
       type: DataTypes.DATE,
+      defaultValue: DataTypes.NOW,
+      allowNull: false,
     },
     created_at_utc: {
       type: DataTypes.DATE,

server/src/services/locationService.ts

@@ -19,7 +19,7 @@ interface CreateLocationTriangulationParams {
   }[];
   ttn_gw: LocationSignal[];
   gnss?: Coordinates;
-  gnss_timestamp: Date;
+  gnss_timestamp?: Date;
 }
 
 interface LocationSignal extends Coordinates {
@@ -67,7 +67,7 @@ export class LocationService {
       ttn_gw_longitude: data.ttn_gw?.longitude,
       gnss_latitude: data.gnss?.latitude,
       gnss_longitude: data.gnss?.longitude,
-      gnss_timestamp: data.gnss_timestamp,
+      gnss_location_at_utc: data.gnss_timestamp,
     });
   }
 
@@ -84,7 +84,7 @@ export class LocationService {
       wifi: wifi_location,
       ttn_gw: gateway_location,
       gnss: data.gnss,
-      
+      gnss_timestamp: data.gnss_timestamp,
     });
   }