Revisions show incorrect date

[LeanderG]

Description

It always showed "2 hours ago" even though I created them a second ago. After the fix it show "just now" correctly.

In dialect-helpers.ts the current time is created with this code:

export function currentTimestamp(db: Kysely<any>): RawBuilder<string> {
	if (isPostgres(db)) {
		return sql`CURRENT_TIMESTAMP`;
	}
	return sql`(datetime('now'))`;
}

(same with the function currentTimestampValue)

My suggestion would be to change it to:

export function currentTimestamp(db: Kysely<any>): RawBuilder<string> {
	if (isPostgres(db)) {
		return sql`CURRENT_TIMESTAMP`;
	}
	return sql`(strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))`;
}

This would avoid bugs when parsing these Dates in JS.
new Date('2026-05-03 17:26:23') is parsed incorrectly while new Date('2026-05-03T17:26:23Z') is parsed correctly.

I'm not sure if a migration would need to be created for this change.

Steps to reproduce

Make a change to a post and then open revisions with a database other than postgres.

Environment

• emdash 0.9.0
• database other than postgres

Logs / error output

[ascorbic]

Yeah, we really should be storing it with the full tz info. Are oyu able to see which part has the problem: are the timestamps stored as UTC and the problem is in the parsing, or does it use local time that makes it impossible to know the real tz?

[LeanderG]

datetime('now') stores timestamps in UTC but omits the Z suffix that indicates the UTC timezone.

Parsers interpret the value as local time and produce incorrect dates. Rather than configuring UTC at every parsing, it’s simpler to store the timezone explicitly in the database.

[ascorbic]

Yeah I'm thinking specifically about whether migrations will be able to know what the timezone is supposed to be

[LeanderG]

Yep, migrations can assume it's stored in UTC. I see currentTimestamp used in a ton of tables so it would be a large migration. 😅

I wouldn't be suprised if this fixes some other bugs not discovered yet.

[ascorbic]

The other option is to skip the big migration. Fix the format for future saves, and in the meantime make the date parsing more forgiving: assume UTC for timestamps without one specified. That way we can handle existing data and gradually fix it when there are edits.

[LeanderG]

There are two parts to the migration:

1. Table definitions (required)
2. Migration of existing data (hoping we can skip this)

The default value is part of the schema definition.
Example after I recreated the DB:

CREATE TABLE "revisions" (
"id" text,
"collection" text NOT NULL,
"entry_id" text NOT NULL,
"data" TEXT NOT NULL,
"author_id" text,
"created_at" text DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
PRIMARY KEY("id")
);

My understanding is we would need to migrate every column that uses currentTimestamp so it uses the new default value for new data.

Affected tables and columns are:
revisions: created_at
media: created_at
users: created_at
audit_logs: timestamp
_emdash_collections: created_at, updated_at
_emdash_fields: created_at
_plugin_storage: created_at, updated_at
_plugin_state: installed_at
_plugin_indexes: created_at
_emdash_menus: created_at, updated_at
_emdash_menu_items: created_at
_emdash_taxonomy_defs: created_at
users_new: created_at, updated_at
credentials: created_at, last_used_at
auth_tokens: created_at
oauth_accounts: created_at
allowed_domains: created_at
auth_challenges: created_at
users_old: created_at
_emdash_api_tokens: created_at
_emdash_oauth_tokens: created_at
_emdash_device_codes: created_at
_emdash_authorization_codes: created_at
_emdash_seo: created_at, last_used_at
_emdash_oauth_clients: created_at, last_used_at
_emdash_cron_task: created_at
_emdash_comments: created_at, last_used_at
_emdash_redirects: created_at, last_used_at
_emdash_404_log: created_at
_emdash_bylines: created_at, last_used_at
_emdash_content_bylines: created_at
tables created with createContentTable: created_at, last_used_at

For revisions I don't think it's a big deal if old revisions stay displayed incorrectly so I wouldn't put any effort in changing the date parsing. I'm not familiar enough with emdash to judge if we should change date parsing of other data or if we should migrate existing data.