Unable to update records when datetime is in primary key

[hummuscience]

Bug Report

Description

I cannot update any field in a record via LabBook when the table has a datetime column in the primary key. Clicking "Update" fails with the error "Update can only be applied to one existing entry", even though the record clearly exists and is displayed in the UI.

I suspect this may be related to timezone handling. I noticed that the datetime displayed in LabBook (06:27:03 GMT) differs from what's stored in the database (08:27:03) by exactly 2 hours - which corresponds to the CET/UTC offset. If the frontend is sending the displayed UTC value back to the server, it wouldn't match the stored value, which could explain the lookup failure.

Reproducibility

• OS: Linux (CentOS on HPC cluster)
• Pharus Version: 0.8.10
• MySQL Version: 8.0
• MySQL Deployment Strategy: remote (MySQL server with system_time_zone = CET)
• DataJoint LabBook: Current version

Steps to reproduce

1. Create a table with datetime in the primary key:

import datajoint as dj

schema = dj.Schema('test_tz_bug')

@schema
class Recording(dj.Manual):
    definition = '''
    subject : varchar(32)
    session_datetime : datetime
    ---
    notes : varchar(255)
    '''

# Insert a record with naive datetime (stored as-is in CET)
Recording.insert1({
    'subject': 'test001',
    'session_datetime': '2025-10-01 08:27:03',
    'notes': 'original'
})

2. Start pharus server on a system where local timezone is CET

3. View the record via LabBook UI:

   • The datetime displays as 06:27:03 GMT (correctly converted from CET to UTC)

4. Try to update the notes field via LabBook UI

5. Error: "Update can only be applied to one existing entry"

Complete error trace

The error occurs in datajoint/table.py:326-328:

key = {k: row[k] for k in self.primary_key}
if len(self & key) != 1:
    raise DataJointError("Update can only be applied to one existing entry.")

The lookup fails because:

• Frontend sends: session_datetime = '2025-10-01 06:27:03' (UTC)
• Database has: session_datetime = '2025-10-01 08:27:03' (CET)

Expected Behavior

Record updates should succeed regardless of the server timezone. The datetime value sent by the frontend should match what's stored in the database.

Screenshots

Additional Research and Context

Suspected root cause

Looking at the pharus source code, I believe the issue may be in pharus/interface.py:

Serialization (line 204-208) - when fetching records:

row.append(
    non_blobs_row[attribute_name]
    .replace(tzinfo=datetime.timezone.utc)  # Assumes naive datetime is UTC
    .timestamp()
)

This converts naive datetimes to epoch by assuming they are UTC. However, MySQL stores naive datetimes, and their actual timezone depends on the server configuration.

My hypothesis for the update failure:

• The frontend receives the epoch, displays it as UTC (e.g., 06:27:03 GMT)
• When updating, the frontend sends back the UTC datetime string
• Pharus passes this directly to DataJoint without conversion
• DataJoint looks for 06:27:03 but database has 08:27:03 → no match

I haven't captured the actual PATCH request to confirm this, but the 2-hour offset matches exactly what this theory would predict.

Demonstration script

import datetime
import os

os.environ['TZ'] = 'CET'  # Simulate server timezone

# Database stores this naive datetime (local time)
db_datetime = datetime.datetime(2025, 10, 1, 8, 27, 3)

# Pharus serialization (interface.py:204-208)
epoch = db_datetime.replace(tzinfo=datetime.timezone.utc).timestamp()

# Frontend displays as UTC
utc_dt = datetime.datetime.fromtimestamp(epoch, tz=datetime.timezone.utc)
print(f"Database has:     {db_datetime}")           # 2025-10-01 08:27:03
print(f"Frontend shows:   {utc_dt.strftime('%H:%M:%S')} GMT")  # 08:27:03 GMT (wrong!)

# But with .timestamp() on naive datetime (older pharus behavior):
epoch_local = db_datetime.timestamp()  # treats as local
utc_from_local = datetime.datetime.fromtimestamp(epoch_local, tz=datetime.timezone.utc)
print(f"Correct UTC:      {utc_from_local.strftime('%H:%M:%S')} GMT")  # 06:27:03 GMT

Possible solutions

1. Configurable timezone: Add PHARUS_DATETIME_TIMEZONE environment variable to specify what timezone naive datetimes are in (default: UTC for backward compatibility)

2. Consistent round-trip: Ensure the same timezone assumption is used for both serialization and deserialization

3. Preserve raw values: Don't convert datetimes to epoch at all - send ISO strings directly to preserve the exact database value

Related code locations

• Serialization: pharus/interface.py:200-208 (_fetch_records)
• Update handling: pharus/interface.py:400-427 (_update_tuple)
• Insert handling: pharus/interface.py:323-345 (_insert_tuple)
• Frontend datetime handling: datajoint-labbook/src/Components/MainTableView/DataStorageClasses/TableAttribute.tsx

Questions for maintainers

1. What is the intended timezone behavior for naive datetimes? Should pharus assume UTC, local time, or make it configurable?

2. Is there interest in a PR that adds timezone configuration, or would a simpler fix (consistent round-trip) be preferred?

3. Are there other parts of the codebase that depend on the current UTC assumption that would need to be updated?