Repository: typedb/typedb-driver-python (language: python)

Issue:
Python client datetimes are influenced by time zone
## Description

The Python client API is inconsistent with time zones. When a `datetime` attribute is inserted, everything is fine. However, when this same attribute is queried, there is an offset of 2 hours (UTC+01:00 + summertime). 

## Environment

1. **OS**: Windows 10
2. **TypeDB version**: TypeDB 2.17.0
3. **TypeDB client-python version:** client-python 2.17.0 (the newest release 2.18.0 crashes at this moment for some reason)
4. **Python version**: Python 3.10.6, Python 3.8.2 and Python 3.7.8 (tested the issue on these three versions)
5. **Other environment details**: Clean `venv` with only `typedb-client` installed

## Reproducible Steps

1. Make sure that the system time is NOT on (UTC+00:00) or summer/wintertime is ENABLED
2. Create a virtual environment and install the client
```bash
python3 -m venv venv               # Create virtual environment in Python 3
.\venv\Scripts\activate            # Activate the virtual environment
pip install typedb-client==2.17.0  # Install the typedb-client (version 2.18.0 crashes)
```
3. Run the TypeDB server
```bash
typedb server  # Version 2.17.0
```
4. Run this minimal test python file
```python
from typedb.client import TypeDB, SessionType, TransactionType


KEYSPACE = "datetime_issue"


def main() -> None:
    # Create a client
    with TypeDB.core_client(TypeDB.DEFAULT_ADDRESS) as client:
        # Create the keyspace if it does not exist
        if not client.databases().contains(KEYSPACE):
            client.databases().create(KEYSPACE)

        # Create a schema session
        with client.session(KEYSPACE, SessionType.SCHEMA) as session:
            with session.transaction(TransactionType.WRITE) as write_schema_transaction:
                # Only define a datetime attribute
                query = "define test_date sub attribute, value datetime;"
                write_schema_transaction.query().define(query)
                write_schema_transaction.commit()

        # Create a data session
        with client.session(KEYSPACE, SessionType.DATA) as session:
            with session.transaction(TransactionType.WRITE) as write_data_transaction:
                # Insert first of every month, at 00:00:00
                query = """insert
                $time_date_jan isa test_date;  $time_date_jan 2023-01-01T00:00:00;
                $time_date_feb isa test_date;  $time_date_feb 2023-02-01T00:00:00;
                $time_date_mar isa test_date;  $time_date_mar 2023-03-01T00:00:00;
                $time_date_apr isa test_date;  $time_date_apr 2023-04-01T00:00:00;
                $time_date_may isa test_date;  $time_date_may 2023-05-01T00:00:00;
                $time_date_jun isa test_date;  $time_date_jun 2023-06-01T00:00:00;
                $time_date_jul isa test_date;  $time_date_jul 2023-07-01T00:00:00;
                $time_date_aug isa test_date;  $time_date_aug 2023-08-01T00:00:00;
                $time_date_sep isa test_date;  $time_date_sep 2023-09-01T00:00:00;
                $time_date_oct isa test_date;  $time_date_oct 2023-10-01T00:00:00;
                $time_date_nov isa test_date;  $time_date_nov 2023-11-01T00:00:00;
                $time_date_dec isa test_date;  $time_date_dec 2023-12-01T00:00:00;
                """
                write_data_transaction.query().insert(query)
                write_data_transaction.commit()

            with session.transaction(TransactionType.READ) as read_data_transaction:
                # Match all date attributes and print them
                query = "match $date isa test_date; get $date;"
                answer = read_data_transaction.query().match(query)  # <- PROBLEM IS HERE
                dates = [ans.get("date") for ans in answer]
                for date in dates:
                    print(f"Retrieved date: {date.get_value()}")


if __name__ == "__main__":
    main()
```


## Expected Output
I would expect to retrieve back the exact same dates as inserted in the `query().insert()`:
```bash
Retrieved date: 2023-01-01 00:00:00
Retrieved date: 2023-02-01 00:00:00
Retrieved date: 2023-03-01 00:00:00
Retrieved date: 2023-04-01 00:00:00
Retrieved date: 2023-05-01 00:00:00
Retrieved date: 2023-06-01 00:00:00
Retrieved date: 2023-07-01 00:00:00
Retrieved date: 2023-08-01 00:00:00
Retrieved date: 2023-09-01 00:00:00
Retrieved date: 2023-10-01 00:00:00
Retrieved date: 2023-11-01 00:00:00
Retrieved date: 2023-12-01 00:00:00
```

## Actual Output
Instead, there is a clear offset in time, 1 hour in wintertime and 2 hours in summertime (exactly UTC+01:00). 
```bash
Retrieved date: 2023-01-01 01:00:00
Retrieved date: 2023-02-01 01:00:00
Retrieved date: 2023-03-01 01:00:00
Retrieved date: 2023-04-01 02:00:00
Retrieved date: 2023-05-01 02:00:00
Retrieved date: 2023-06-01 02:00:00
Retrieved date: 2023-07-01 02:00:00
Retrieved date: 2023-08-01 02:00:00
Retrieved date: 2023-09-01 02:00:00
Retrieved date: 2023-10-01 02:00:00
Retrieved date: 2023-11-01 01:00:00
Retrieved date: 2023-12-01 01:00:00
```
 
## Additional information
- **Issue is in the Python client (TypeDB Studio works fine)**
   Doing a read data `match` query in the `datetime_issue` keyspace inside of TypeDB Studio 2.11.0 on Windows, does NOT have this time offset. This means that the problem is clearly in the Python API, especially in the `transaction.query().match(query)` method.
- **It depends on the system time**
  When the system time in Windows is changed to UTC+00:00 and summer-/wintertime is disabled, there is no time offset.



Discussion / hints:
I met the same issue, does anybody have a solution?
Hi all, we're working on this :) It comes from the fact that the we've used the local date time with your system time zone when sending data to TypeDB. It shouldn't be too hard to resolve but we want to make sure to be systematic about it. We're hoping to have this fixed and released early next week

Fix patch:
diff --git a/.factory/automation.yml b/.factory/automation.yml
index 5fa9a32a..ee999c62 100644
--- a/.factory/automation.yml
+++ b/.factory/automation.yml
@@ -113,6 +113,7 @@ build:
       type: foreground
       command: |
         export PATH="$HOME/.local/bin:$PATH"
+        sudo apt-get update
         sudo apt install python3-pip -y
         python3 -m pip install -U pip
         python3 -m pip install -r requirements_dev.txt
diff --git a/dependencies/vaticle/repositories.bzl b/dependencies/vaticle/repositories.bzl
index 8dfb72d9..573e2bc1 100644
--- a/dependencies/vaticle/repositories.bzl
+++ b/dependencies/vaticle/repositories.bzl
@@ -39,5 +39,6 @@ def vaticle_typedb_behaviour():
     git_repository(
         name = "vaticle_typedb_behaviour",
         remote = "https://github.com/vaticle/typedb-behaviour",
-        commit = "767bf98fef7383addf42a1ae6e97a44874bb4f0b" # sync-marker: do not remove this comment, this is used for sync-dependencies by @vaticle_typedb_behaviour
+        commit = "90b4addcd71a398e9e52e322021c49310e66a47a" # sync-marker: do not remove this comment, this is used for sync-dependencies by @vaticle_typedb_behaviour
     )
+
diff --git a/tool/behave_rule.bzl b/tool/behave_rule.bzl
index 31a528f4..e9ef3fe8 100644
--- a/tool/behave_rule.bzl
+++ b/tool/behave_rule.bzl
@@ -118,7 +118,7 @@ def _rule_implementation(ctx):
     cmd += " && rm -rf " + steps_out_dir
     cmd += " && mkdir " + steps_out_dir + " && "
     cmd += " && ".join(["cp %s %s" % (step_file.path, steps_out_dir) for step_file in ctx.files.steps])
-    cmd += " && behave %s --no-capture -D port=$PORT && export RESULT=0 || export RESULT=1" % feats_dir
+    cmd += " && python3 -m behave %s --no-capture -D port=$PORT && export RESULT=0 || export RESULT=1" % feats_dir
     cmd += """
            echo Tests concluded with exit value $RESULT
            echo Stopping server.
diff --git a/typedb/common/exception.py b/typedb/common/exception.py
index ec601598..ab35cc2f 100644
--- a/typedb/common/exception.py
+++ b/typedb/common/exception.py
@@ -116,7 +116,7 @@ def __init__(self, code: int, message: str):
 NONEXISTENT_EXPLAINABLE_CONCEPT = ConceptErrorMessage(10, "The concept identified by '%s' is not explainable.")
 NONEXISTENT_EXPLAINABLE_OWNERSHIP = ConceptErrorMessage(11, "The ownership by owner '%s' of attribute '%s' is not explainable.")
 GET_HAS_WITH_MULTIPLE_FILTERS = ConceptErrorMessage(12, "Only one filter can be applied at a time to get_has. The possible filters are: [attribute_type, attribute_types, annotations]")
-
+UNSUPPORTED_TIMEZONE_INFORMATION = ConceptErrorMessage(13, "A date-time attribute cannot accept timezone aware datetime objects.")
 
 class QueryErrorMessage(ErrorMessage):
 
diff --git a/typedb/concept/proto/concept_proto_builder.py b/typedb/concept/proto/concept_proto_builder.py
index e31a4871..9c74027e 100644
--- a/typedb/concept/proto/concept_proto_builder.py
+++ b/typedb/concept/proto/concept_proto_builder.py
@@ -25,7 +25,7 @@
 from typedb.api.concept.type.role_type import RoleType
 from typedb.api.concept.type.thing_type import ThingType, Annotations, Annotation
 from typedb.api.concept.type.type import Type
-from typedb.common.exception import TypeDBClientException, BAD_ENCODING, BAD_ANNOTATION
+from typedb.common.exception import TypeDBClientException, BAD_ENCODING, BAD_ANNOTATION, UNSUPPORTED_TIMEZONE_INFORMATION
 from typedb.common.rpc.request_builder import proto_role_type, proto_thing_type, byte_string
 
 
@@ -83,6 +83,8 @@ def string_value(value: str):
 
 
 def datetime_value(value: datetime):
+    if value.tzinfo is not None:
+        raise TypeDBClientException.of(UNSUPPORTED_TIMEZONE_INFORMATION)
     value_proto = concept_proto.ConceptValue()
     value_proto.date_time = int((value - datetime(1970, 1, 1)).total_seconds() * 1000)
     return value_proto
diff --git a/typedb/concept/thing/attribute.py b/typedb/concept/thing/attribute.py
index f45aef6e..e6c8a41d 100644
--- a/typedb/concept/thing/attribute.py
+++ b/typedb/concept/thing/attribute.py
@@ -211,7 +211,7 @@ def __init__(self, iid: str, is_inferred: bool, type_: DateTimeAttributeType, va
 
     @staticmethod
     def of(thing_proto: concept_proto.Thing):
-        return _DateTimeAttribute(concept_proto_reader.iid(thing_proto.iid), thing_proto.inferred, concept_proto_reader.attribute_type(thing_proto.type), datetime.fromtimestamp(float(thing_proto.value.date_time) / 1000.0))
+        return _DateTimeAttribute(concept_proto_reader.iid(thing_proto.iid), thing_proto.inferred, concept_proto_reader.attribute_type(thing_proto.type), datetime.utcfromtimestamp(float(thing_proto.value.date_time) / 1000.0))
 
     def get_type(self) -> "DateTimeAttributeType":
         return self._type
diff --git a/typedb/concept/value/value.py b/typedb/concept/value/value.py
index 4de6ea63..22137c8e 100644
--- a/typedb/concept/value/value.py
+++ b/typedb/concept/value/value.py
@@ -111,7 +111,7 @@ def __init__(self, value: datetime):
 
     @staticmethod
     def of(value_proto: concept_proto.Value):
-        return _DateTimeValue(datetime.fromtimestamp(float(value_proto.value.date_time) / 1000.0))
+        return _DateTimeValue(datetime.utcfromtimestamp(float(value_proto.value.date_time) / 1000.0))
 
     def get_value(self):
         return self._value


Test patch:
diff --git a/tests/behaviour/concept/serialization/json/BUILD b/tests/behaviour/concept/serialization/json/BUILD
index 63f04191..ab5cd348 100644
--- a/tests/behaviour/concept/serialization/json/BUILD
+++ b/tests/behaviour/concept/serialization/json/BUILD
@@ -40,7 +40,7 @@ typedb_behaviour_py_test(
         "//tests/behaviour/connection:steps",
         "//tests/behaviour/connection/database:steps",
         "//tests/behaviour/connection/session:steps",
-        "//tests/behaviour/connection/transaction:steps",
+        "//tests/behaviour/connection/transaction:steps"
     ],
     deps = [
         "//:client_python",
diff --git a/tests/behaviour/concept/thing/attribute/BUILD b/tests/behaviour/concept/thing/attribute/BUILD
index a1b7a110..bf2ffb87 100644
--- a/tests/behaviour/concept/thing/attribute/BUILD
+++ b/tests/behaviour/concept/thing/attribute/BUILD
@@ -43,6 +43,7 @@ typedb_behaviour_py_test(
         "//tests/behaviour/connection/database:steps",
         "//tests/behaviour/connection/session:steps",
         "//tests/behaviour/connection/transaction:steps",
+        "//tests/behaviour/util:util_steps"
     ],
     deps = [
         "//:client_python",
diff --git a/tests/behaviour/concept/thing/attribute/attribute_steps.py b/tests/behaviour/concept/thing/attribute/attribute_steps.py
index 5ca524a3..c29bf06d 100644
--- a/tests/behaviour/concept/thing/attribute/attribute_steps.py
+++ b/tests/behaviour/concept/thing/attribute/attribute_steps.py
@@ -149,3 +149,4 @@ def step_impl(context: Context, var: str, value: str):
 @step("attribute {var:Var} has datetime value: {value:DateTime}")
 def step_impl(context: Context, var: str, value: datetime):
     assert_that(context.get(var).as_attribute().get_value(), is_(value))
+
diff --git a/tests/behaviour/config/parameters.py b/tests/behaviour/config/parameters.py
index 399ef81e..c172ead8 100644
--- a/tests/behaviour/config/parameters.py
+++ b/tests/behaviour/config/parameters.py
@@ -58,11 +58,18 @@ def parseWords(text):
 
 
 @parse.with_pattern(r"\d\d\d\d-\d\d-\d\d(?: \d\d:\d\d:\d\d)?")
+def parse_datetime_pattern(text: str) -> datetime:
+    return parse_datetime(str)
+
+
 def parse_datetime(text: str) -> datetime:
     try:
-        return datetime.strptime(text, "%Y-%m-%d %H:%M:%S")
+        return datetime.strptime(text, "%Y-%m-%dT%H:%M:%S")
     except ValueError:
-        return datetime.strptime(text, "%Y-%m-%d")
+        try:
+            return datetime.strptime(text, "%Y-%m-%d %H:%M:%S")
+        except ValueError:
+            return datetime.strptime(text, "%Y-%m-%d")
 
 
 register_type(DateTime=parse_datetime)
diff --git a/tests/behaviour/typeql/language/insert/BUILD b/tests/behaviour/typeql/language/insert/BUILD
index e0ea046c..be27be80 100644
--- a/tests/behaviour/typeql/language/insert/BUILD
+++ b/tests/behaviour/typeql/language/insert/BUILD
@@ -34,6 +34,7 @@ typedb_behaviour_py_test(
         "//tests/behaviour/connection/session:steps",
         "//tests/behaviour/connection/transaction:steps",
         "//tests/behaviour/typeql:steps",
+        "//tests/behaviour/util:util_steps"
     ],
     deps = [
         "//:client_python",
diff --git a/tests/behaviour/typeql/typeql_steps.py b/tests/behaviour/typeql/typeql_steps.py
index 30b74858..465fafb1 100644
--- a/tests/behaviour/typeql/typeql_steps.py
+++ b/tests/behaviour/typeql/typeql_steps.py
@@ -225,7 +225,7 @@ class AttributeMatcher(ConceptMatcher, ABC):
 
     def __init__(self, type_and_value: str):
         self.type_and_value = type_and_value
-        s = type_and_value.split(":")
+        s = type_and_value.split(":", 1)
         assert_that(s, has_length(2),
                     "[%s] is not a valid attribute identifier. It should have format \"type_label:value\"." % type_and_value)
         self.type_label, self.value_string = s
@@ -284,7 +284,7 @@ class ValueMatcher(ConceptMatcher):
 
     def __init__(self, value_type_and_value: str):
         self.value_type_and_value = value_type_and_value
-        s = value_type_and_value.split(":")
+        s = value_type_and_value.split(":", 1)
         assert_that(s, has_length(2),
                     "[%s] is not a valid identifier. It should have format \"value_type:value\"." % value_type_and_value)
         self.value_type_name, self.value_string = s
diff --git a/tests/behaviour/util/util_steps.py b/tests/behaviour/util/util_steps.py
index 8fa555f7..3827606a 100644
--- a/tests/behaviour/util/util_steps.py
+++ b/tests/behaviour/util/util_steps.py
@@ -25,6 +25,13 @@
 
 from tests.behaviour.context import Context
 
+import os
+import time
+
+@step("set time-zone is: {time_zone_label}")
+def step_impl(context: Context, time_zone_label: str):
+    os.environ["TZ"] = time_zone_label
+    time.tzset()
 
 @step("wait {seconds} seconds")
 def step_impl(context: Context, seconds: str):