C#•2mo ago

✅ How to save to database with ISO format?

Hello, I'd like to save datetime with timezone to database. I use NodaTime for this. Let's look into my code: BaseEntity:

public class Entity
{
    public required Guid Id { get; set; }

    public LocalDateTime CreatedAt { get; protected set; }
    
    public DateTimeZone CreatedAtTz { get; protected set; }

    protected Entity()
    {
        Id = Guid.NewGuid();
        CreatedAt = LocalDateTime.FromDateTime(DateTime.UtcNow);
        CreatedAtTz = SystemClock.Instance.GetTimeZone();
    }
}

public class Entity
{
    public required Guid Id { get; set; }

    public LocalDateTime CreatedAt { get; protected set; }
    
    public DateTimeZone CreatedAtTz { get; protected set; }

    protected Entity()
    {
        Id = Guid.NewGuid();
        CreatedAt = LocalDateTime.FromDateTime(DateTime.UtcNow);
        CreatedAtTz = SystemClock.Instance.GetTimeZone();
    }
}

GetTimeZone extension to automatically get time zone:

public static class DateTimeExtensions
{
    public static DateTimeZone GetTimeZone(this SystemClock systemClock)
    {
        var instance = systemClock.GetCurrentInstant();
        var zone = DateTimeZoneProviders.Bcl.GetSystemDefault();
        
        return instance.InZone(zone).Zone;
    }
}

public static class DateTimeExtensions
{
    public static DateTimeZone GetTimeZone(this SystemClock systemClock)
    {
        var instance = systemClock.GetCurrentInstant();
        var zone = DateTimeZoneProviders.Bcl.GetSystemDefault();
        
        return instance.InZone(zone).Zone;
    }
}

It works good. I see almost correctly filled date and time zone in database when user creates entity. CreatedAt should be in ISO format but it looks not like ISO format in database

4 Replies

Jimmacle•2mo ago

what database? unless you're storing it as text, the display format isn't the same as the storage format

SWEETPONYOP•2mo ago

postgres

Jimmacle•2mo ago

so that's just how your database editor displays it, it isn't actually stored that way postgres datetimes are just a unix timestamp iirc

SWEETPONYOP•2mo ago

ah I see ddl ur right

create table "UserAccount"
(
    "Id"          uuid                  not null
        constraint "PK_UserAccount"
            primary key,
    "Username"    text                  not null,
    "Email"       text                  not null,
    "IpAddress"   inet                  not null,
    "CreatedAt"   timestamp             not null,
    "CreatedAtTz" text default ''::text not null
);

create table "UserAccount"
(
    "Id"          uuid                  not null
        constraint "PK_UserAccount"
            primary key,
    "Username"    text                  not null,
    "Email"       text                  not null,
    "IpAddress"   inet                  not null,
    "CreatedAt"   timestamp             not null,
    "CreatedAtTz" text default ''::text not null
);

Gaming

Programming

✅ How to save to database with ISO format?