Verify that casting a datetime2 to datetimeoffset sets the timezone to UTC and does not change timestamp

[hohonuuli]

See https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver15

[hohonuuli]

It looks like when altering the table from datetime2 to datetimeoffset, the dates are copied across with no timezone information. If that's true using AT TIME ZONE 'UTC' will simply add the timezone without altering the data. I need to verify that is what happens. Example conversion is:

alter table MyTable 
   alter column DateTimeColumn datetimeoffset;

update Mytable
   set DateTimeColumn = convert(datetime2, DateTimeColumn) AT TIME ZONE 'UTC'

[hohonuuli]

I can get a list of timezone names in SQL Server using:

SELECT * FROM sys.time_zone_info

[hohonuuli]

For postgres, take a look at https://dba.stackexchange.com/questions/134385/convert-postgres-timestamp-to-timestamptz

[hohonuuli]

Sanity check:

annosaurus 0.16.0

http://m3.shore.mbari.org/anno/v1/imagedmoments/2279d37a-e5c3-446f-9c53-2cb29c104beb

{
  "recorded_date": "2013-11-10T19:30:29Z",
  "timecode": "05:03:12:17",
  "video_reference_uuid": "f79a0734-36cb-473a-92a0-9cc3582efb8b",
  "observations": [
    {
      "concept": "Procymbulia",
      "observation_timestamp": "2013-11-10T19:35:00.210Z",
      "observer": "kwalz",
      "group": "ROV",
      "activity": "descend",
      "associations": [
        {
          "link_name": "identity-reference",
          "link_value": "10",
          "to_concept": "self",
          "mime_type": "text/plain",
          "last_updated_time": "2018-12-31T07:23:35Z",
          "uuid": "4a556ab1-6543-486c-b698-12f1559b385a"
        },
        {
          "link_name": "bounding box",
          "link_value": "{"x": 426, "y": 179, "width": 125, "height": 59, "observer": "kakani", "strength": "Jedi Knight", "generator": "vars-localize", "project": "FathomNet", "image_reference_uuid": "e85ae583-610f-47f3-9dca-c6d2ce5c3386"}",
          "to_concept": "self",
          "mime_type": "application/json",
          "last_updated_time": "2022-02-01T11:53:30Z",
          "uuid": "d760339e-9086-42a3-016e-34168a39c81e"
        }
      ],
      "last_updated_time": "2018-12-31T07:23:35Z",
      "uuid": "221740ba-3fda-4025-8a12-11fb30d03cc9"
    }
  ],
  "image_references": [
    {
      "description": "source image",
      "url": "http://search.mbari.org/ARCHIVE/frameGrabs/Doc%20Ricketts/images/0547/05_03_12_17.png",
      "height_pixels": 486,
      "width_pixels": 720,
      "format": "image/png",
      "last_updated_time": "2018-12-31T07:23:35Z",
      "uuid": "e85ae583-610f-47f3-9dca-c6d2ce5c3386"
    },
    {
      "description": "compressed image with overlay",
      "url": "http://search.mbari.org/ARCHIVE/frameGrabs/Doc%20Ricketts/images/0547/05_03_12_17.jpg",
      "height_pixels": 486,
      "width_pixels": 720,
      "format": "image/jpg",
      "last_updated_time": "2018-12-31T07:23:36Z",
      "uuid": "a3b0ea87-73be-47ab-b7d9-f4c62b36e47b"
    }
  ],
  "ancillary_data": {
    "oxygen_ml_l": 0.2529999911785126,
    "depth_meters": 821.2415771484375,
    "latitude": 36.548835,
    "temperature_celsius": 4.544000148773193,
    "theta": 3.5999999046325684,
    "longitude": -122.511124,
    "phi": -3.700000047683716,
    "psi": 190.3000030517578,
    "pressure_dbar": 838.7999877929688,
    "salinity": 34.4119987487793,
    "altitude": 0,
    "light_transmission": 79.45999908447266,
    "last_updated_time": "2023-04-01T16:17:20Z",
    "uuid": "92f3850c-bd03-4bfe-a8e9-7c3433a9b396"
  },
  "last_updated_time": "2019-02-11T11:28:23Z",
  "uuid": "2279d37a-e5c3-446f-9c53-2cb29c104beb"
}

annosaurus 0.16.0-113-g7ff613b-SNAPSHOT

http://localhost:8082/v1/imagedmoments/2279d37a-e5c3-446f-9c53-2cb29c104beb

{
  "video_reference_uuid": "f79a0734-36cb-473a-92a0-9cc3582efb8b",
  "timecode": "05:03:12:17",
  "recorded_timestamp": "2013-11-10T19:30:29Z",
  "observations": [
    {
      "concept": "Procymbulia",
      "group": "ROV",
      "activity": "descend",
      "observer": "kwalz",
      "observation_timestamp": "2013-11-10T19:35:00.210Z",
      "associations": [
        {
          "link_name": "identity-reference",
          "to_concept": "self",
          "link_value": "10",
          "mime_type": "text/plain",
          "uuid": "4a556ab1-6543-486c-b698-12f1559b385a",
          "last_updated_time": "2018-12-31T07:23:35.963Z"
        },
        {
          "link_name": "bounding box",
          "to_concept": "self",
          "link_value": "{"x": 426, "y": 179, "width": 125, "height": 59, "observer": "kakani", "strength": "Jedi Knight", "generator": "vars-localize", "project": "FathomNet", "image_reference_uuid": "e85ae583-610f-47f3-9dca-c6d2ce5c3386"}",
          "mime_type": "application/json",
          "uuid": "d760339e-9086-42a3-016e-34168a39c81e",
          "last_updated_time": "2022-02-01T11:53:30.620Z"
        }
      ],
      "uuid": "221740ba-3fda-4025-8a12-11fb30d03cc9",
      "last_updated_time": "2018-12-31T07:23:35.963Z"
    }
  ],
  "image_references": [
    {
      "url": "http://search.mbari.org/ARCHIVE/frameGrabs/Doc%20Ricketts/images/0547/05_03_12_17.jpg",
      "format": "image/jpg",
      "width_pixels": 720,
      "height_pixels": 486,
      "description": "compressed image with overlay",
      "uuid": "a3b0ea87-73be-47ab-b7d9-f4c62b36e47b",
      "last_updated_time": "2018-12-31T07:23:36.040Z"
    },
    {
      "url": "http://search.mbari.org/ARCHIVE/frameGrabs/Doc%20Ricketts/images/0547/05_03_12_17.png",
      "format": "image/png",
      "width_pixels": 720,
      "height_pixels": 486,
      "description": "source image",
      "uuid": "e85ae583-610f-47f3-9dca-c6d2ce5c3386",
      "last_updated_time": "2018-12-31T07:23:35.993Z"
    }
  ],
  "ancillary_data": {
    "latitude": 36.548835,
    "longitude": -122.511124,
    "depth_meters": 821.2416,
    "altitude": 0,
    "salinity": 34.412,
    "temperature_celsius": 4.544,
    "oxygen_ml_l": 0.253,
    "pressure_dbar": 838.8,
    "light_transmission": 79.46,
    "phi": -3.700000047683716,
    "theta": 3.5999999046325684,
    "psi": 190.3000030517578,
    "uuid": "92f3850c-bd03-4bfe-a8e9-7c3433a9b396",
    "last_updated_time": "2023-04-01T16:17:20.740520500Z"
  },
  "uuid": "2279d37a-e5c3-446f-9c53-2cb29c104beb",
  "last_updated_time": "2024-02-08T00:10:43.671982Z"
}

[hohonuuli]

vampire-squid 0.5.2

http://m3.shore.mbari.org/vam/v1/media/videoreference/f79a0734-36cb-473a-92a0-9cc3582efb8b

{
  "video_sequence_uuid": "2c13a92d-00a1-435a-b901-a9ae2de8d5e5",
  "video_reference_uuid": "f79a0734-36cb-473a-92a0-9cc3582efb8b",
  "video_uuid": "8f94102a-1944-40e3-a5b5-379a1bf614b7",
  "video_sequence_name": "Doc Ricketts 0547",
  "camera_id": "Doc Ricketts",
  "video_name": "D0547-05HD",
  "uri": "urn:tid:mbari.org:D0547-05HD",
  "start_timestamp": "2013-11-10T18:38:28Z",
  "duration_millis": 3786000,
  "container": "tape",
  "width": 1920,
  "height": 1080,
  "frame_rate": 29.97,
  "size_bytes": 0,
  "description": "Tape loaded from VARS on 2018-12-31T15:23:34.359Z. 10/10 valid dates"
}

vampire-squid 0.5.2-78-g748cf48

http://localhost:8084/v1/media/videoreference/f79a0734-36cb-473a-92a0-9cc3582efb8b

{
  "video_sequence_uuid": "2c13a92d-00a1-435a-b901-a9ae2de8d5e5",
  "video_uuid": "8f94102a-1944-40e3-a5b5-379a1bf614b7",
  "video_reference_uuid": "f79a0734-36cb-473a-92a0-9cc3582efb8b",
  "video_sequence_name": "Doc Ricketts 0547",
  "camera_id": "Doc Ricketts",
  "video_name": "D0547-05HD",
  "uri": "urn:tid:mbari.org:D0547-05HD",
  "start_timestamp": "2013-11-10T18:38:28Z",
  "duration_millis": 3786000,
  "container": "tape",
  "width": 1920,
  "height": 1080,
  "frame_rate": 29.97,
  "size_bytes": 0,
  "description": "Tape loaded from VARS on 2018-12-31T15:23:34.359Z. 10/10 valid dates"
}

[hohonuuli]

Looks good. The recorded_timestamp/recorded_date in annosaurus match. The start_timestamp in vampire-squid matches.