Unexpected behavior with DateTimeOffset ?

markhilb commented 1 year ago

While inserting and retrieving DateTimeOffset values from a SQL Server database, I noticed a surprising behavior where the inserted value is stored differently in the database than what was inserted and retrieved.

Setup

main.rs

use tiberius::{
    time::chrono::{DateTime, FixedOffset},
    AuthMethod, Client, Config,
};
use tokio::net::TcpStream;
use tokio_util::compat::TokioAsyncWriteCompatExt;

#[tokio::main]
async fn main() {
    let mut config = Config::new();

    config.host("0.0.0.0");
    config.port(1433);
    config.authentication(AuthMethod::sql_server("sa", "super_secret_password12"));

    config.trust_cert();

    let tcp = TcpStream::connect(config.get_addr()).await.unwrap();
    tcp.set_nodelay(true).unwrap();

    let mut client = Client::connect(config, tcp.compat_write()).await.unwrap();

    let _ = client
        .execute(
            r#"
DROP TABLE Test
           "#,
            &[],
        )
        .await;

    client
        .execute(
            r#"
CREATE TABLE
    Test (
        id BIGINT IDENTITY(1, 1) PRIMARY KEY,
        field DATETIMEOFFSET NOT NULL
    )
           "#,
            &[],
        )
        .await
        .unwrap();

    let d = "2022-05-20T11:30:11.642+02:00"
        .parse::<DateTime<FixedOffset>>()
        .unwrap();

    dbg!(&d);

    client
        .execute(
            r#"
INSERT INTO
    Test (field)
VALUES
    (@P1);
           "#,
            &[&d],
        )
        .await
        .unwrap();

    client
        .query(
            r#"
SELECT
    *
FROM
    Test
           "#,
            &[],
        )
        .await
        .unwrap()
        .into_row()
        .await
        .unwrap()
        .map(|r| {
            let d: DateTime<FixedOffset> = r.get("field").unwrap();
            dbg!(d)
        });
}

Cargo.toml

[package]
name = "tiberius-test"
version = "0.1.0"
edition = "2021"

[dependencies]
chrono = "0.4.23"
tiberius = { version = "0.11.3", features = ["chrono"] }
tokio = { version = "1.22.0", features = ["full"] }
tokio-util = { version = "0.7.4", features = ["compat"] }

docker-compose.yml

version: "3.8"

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: mssql
    environment:
      SA_PASSWORD: super_secret_password12
      ACCEPT_EULA: Y
    ports:
      - 1433:1433

Execution

$ docker-compose up -d
$ cargo run

    Finished dev [unoptimized + debuginfo] target(s) in 0.03s
     Running `target/debug/tiberius-test`
[src/main.rs:50] &d = 2022-05-20T11:30:11.642+02:00
[src/main.rs:82] d = 2022-05-20T11:30:11.642+02:00

$ sqlcmd -S "localhost,1433" -C -U sa -P super_secret_password12
1> select * from Test;
2> go
id                   field
-------------------- ---------------------------------------------
                   1            2022-05-20 13:30:11.6420000 +02:00

(1 rows affected)

Result

As shown, the inserted and retrieved DateTimeOffset values are the same while using tiberius. However, when looking at the actual value stored in the database using something like sqlcmd there is a different value. The inserted value was 2022-05-20T11:30:11.642+02:00, but the database shows the value to be 2022-05-20 13:30:11.6420000 +02:00. This means that if you interact with the database using something other than tiberius you would get the wrong value.

Is this the intended outcome, or a bug?

pimeys commented 1 year ago

Hey.

So... the value is not stored as a string in the database, but with the following setup:

For datetimeoffset(n), we

first store the length of the whole type (which is the length of the time component plus five extra bytes),
encode the time component, based on the length (n in the type), is either as u16 + u8, u32 or u32 + u8
encode the number of days to the given date from 1st of January year 1
encode the given offset as i16

This is in the TDS standard, and comparison is done in the byte level, not by string. The database can render the value how it wants, but under the surface the storage is as written here. E.g. not a bug, unless you can find a test that clearly shows we do something wrong!

markhilb commented 1 year ago

Yes, I understand how dates are stored and displayed in a database.

The issue I observed is that the way tiberius stores the values does not seem to be compatible with other languages.

For example, if I insert the same date 2022-05-20T11:30:11.642+02:00 using C# or python, they are displayed in the database as 2022-05-20T11:30:11.642+02:00, and retrieved from the database as the same value.

However, if I insert that value using tiberius, it is displayed in the database as 2022-05-20 13:30:11.6420000 +02:00 and retrieving that value using C# or python will also yield 2022-05-20 13:30:11.6420000 +02:00, which is inconsistent with tiberius, which retrieves the value as 2022-05-20T11:30:11.642+02:00.

The expected behavior should be that inserting and retrieving a date should yield the same value regardless of which language does the inserting and retrieving. However, this is not the case here.

pimeys commented 1 year ago

Hmm, I'd love to see a test and PR to fix this, if you find what we do differently!

prisma / tiberius