Cannot get posts from the same date (SQLite3-specific?)

[cathysarisky]

Issue Summary

In sqlite3, dates are formatted in the published_at column of the posts table as '2025-04-22 14:13:46'. (Note the space, lack of T, etc.)
Using {{published_at}} in the theme gives you something like: 2025-04-22T10:13:46.000-04:00
So let's say I want posts newer and older than my current page's publication date.

Posts after: {{published_at}}

{{#get "posts" filter="published_at:>{{published_at}}"}} {{#foreach posts}}

{{date published_at format="YYYY-MM-DDTHH:MM:SSZ"}}

{{/foreach}} {{/get}}

=====

Posts before: {{published_at}}

{{#get "posts" filter="published_at:<{{published_at}}"}} {{#foreach posts}}

{{date published_at format="YYYY-MM-DDTHH:MM:SSZ"}}

{{/foreach}} {{/get}} I have a bunch of posts published on 2-27 -- take a look at what happens with that code: Posts after: 2025-02-27T19:03:00.000-05:00 2025-04-07T11:04:00-04:00 2025-03-13T20:03:00-04:00 2025-03-13T20:03:00-04:00 2025-03-13T20:03:00-04:00 2025-03-13T20:03:00-04:00 2025-02-27T19:02:00-05:00 2025-02-27T19:02:00-05:00 2025-02-27T19:02:00-05:00 2025-02-27T19:02:00-05:00 2025-02-27T19:02:00-05:00 =====

Posts before: 2025-02-27T19:03:00.000-05:00
2025-02-13T19:02:00-05:00
2025-02-13T19:02:00-05:00
2025-02-13T19:02:00-05:00
2025-02-13T19:02:00-05:00
2025-02-13T19:02:00-05:00
2025-01-23T19:01:00-05:00
2025-01-23T19:01:00-05:00
2025-01-23T19:01:00-05:00
I'm getting the posts published at 2025-02-13T19:02:00-05:00 showing up as being after 2025-02-27T19:02:00-05:00
My guess here is that the filter is using the format the posts are in from the database. But there's no T in that date format, so everything from that date is after the published_at, because T > " "

Steps to Reproduce

see above

Ghost Version

5.116

Node.js Version

20

How did you install Ghost?

local install

Database type

SQLite3

Browser & OS version

No response

Relevant log / error output

Code of Conduct

• I agree to be friendly and polite to people in this repository