Timestamp Handling in Bartendie

Timestamp Handling in Bartendie

This document outlines the standardized approach to timestamp handling throughout the Bartendie application, as established by ADR-0032: Standardize UTC timestamps with microsecond precision.

Overview

All timestamps in the Bartendie application use UTC timezone with microsecond precision for consistency, accuracy, and proper timezone handling.

Database Schema

Ecto Schemas

All Ecto schemas use timestamps(type: :utc_datetime_usec) for automatic timestamp fields:

schema "events" do
  field :name, :string
  field :description, :string
  # ... other fields

  timestamps(type: :utc_datetime_usec)
end

Manual Timestamp Fields

For custom timestamp fields, use :utc_datetime_usec type:

schema "shopping_lists" do
  field :name, :string
  field :generated_at, :utc_datetime_usec

  timestamps(type: :utc_datetime_usec)
end

GraphQL Schema

All timestamp fields in GraphQL schemas use the :datetime scalar type:

object :event do
  field :id, :id
  field :name, :string
  field :created_at, :datetime
  field :updated_at, :datetime
end

Application Code

Creating Timestamps

Always use DateTime.utc_now() for creating timestamps:

# Good
timestamp = DateTime.utc_now()

# For explicit microsecond precision
timestamp = DateTime.utc_now(:microsecond)

# Bad - don't use NaiveDateTime
timestamp = NaiveDateTime.utc_now()

Event Sourcing

Event sourcing projections should use UTC DateTime:

def handle(%EventCreated{} = event, _metadata) do
  %Event{
    id: event.event_id,
    name: event.name,
    inserted_at: DateTime.utc_now(),
    updated_at: DateTime.utc_now()
  }
  |> Repo.insert()
end

Frontend Integration

Date Input Handling

The frontend converts date inputs to the correct format for GraphQL:

// Convert datetime input to date-only format for GraphQL
const dateForGraphQL = new Date(formData.date).toISOString().split('T')[0];

// Example: "2025-06-25T14:22:00.000Z" becomes "2025-06-25"

Timestamp Display

GraphQL returns timestamps in ISO 8601 format with microsecond precision:

{
  "createdAt": "2025-06-19T15:30:45.123456Z",
  "updatedAt": "2025-06-19T15:30:45.123456Z"
}

Migration Notes

The migration from naive_datetime to utc_datetime_usec was performed in migration 20250619151518_convert_timestamps_to_utc_datetime_usec.exs. This migration:

1. Converts all existing timestamp columns to :utc_datetime_usec
2. Assumes existing naive timestamps were in UTC (safe assumption for our data)
3. Preserves all existing timestamp data
4. Provides rollback capability

Configuration

The application is configured to use UTC timestamps by default:

config/config.exs

config :bartendie,
  ecto_repos: [Bartendie.Repo],
  generators: [timestamp_type: :utc_datetime_usec]

Benefits

1. Consistent API Responses: All GraphQL timestamp fields serialize correctly
2. Timezone Handling: Proper support for users in different timezones
3. Event Ordering: Microsecond precision enables accurate ordering of high-frequency events
4. Integration: External systems can rely on consistent, timezone-aware timestamp format
5. Debugging: Clear understanding that all timestamps are UTC eliminates timezone confusion

Best Practices

1. Always use UTC: Never store local time without timezone information
2. Use DateTime.utc_now(): For creating new timestamps in application code
3. Microsecond precision: Use :utc_datetime_usec for all new timestamp fields
4. GraphQL consistency: Use :datetime scalar type for all timestamp fields
5. Frontend conversion: Convert datetime inputs to appropriate format for GraphQL

Testing

When writing tests, ensure you account for timezone-aware timestamps:

test "creates event with proper timestamps" do
  before_creation = DateTime.utc_now()

  {:ok, event} = Events.create_event(%{name: "Test Event"})

  after_creation = DateTime.utc_now()

  assert DateTime.compare(event.inserted_at, before_creation) in [:gt, :eq]
  assert DateTime.compare(event.inserted_at, after_creation) in [:lt, :eq]
end

Troubleshooting

Common Issues

1. GraphQL Serialization Errors: Ensure GraphQL schema uses :datetime not :naive_datetime
2. Type Mismatches: Verify Ecto schemas use timestamps(type: :utc_datetime_usec)
3. Frontend Date Format: Ensure date inputs are converted to correct format for GraphQL

Migration Issues

If you encounter issues after the timestamp migration:

1. Check that all GraphQL schema types use :datetime
2. Verify all Ecto schemas use :utc_datetime_usec
3. Ensure application code uses DateTime.utc_now()
4. Test GraphQL queries to confirm proper serialization