IoTSharp / IoTSharp/SonnetDB

[SQL] 提供明确的 date_diff 和日期格式化能力

Open
#175 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
89
Forks
15
Avg merge
2d 9h
Merged PRs (30d)
33

Description

此需求在 SqlSugar 的 SonnetDB Provider 兼容性验证中发现。它仅请求 SonnetDB 的通用 SQL 能力,不要求采用 SqlSugar 的实现。

问题

SonnetDB 已支持 date_partdate_add、Unix 时间转换,但没有已确认的 date_diff 和日期格式化函数。用秒数相减或多个 date_part 相减不能保真 DateDiff 的跨月、跨年、时区和边界计数语义。

最小复现
SELECT date_diff('day', started_at, finished_at) AS day_diff,
       date_diff('month', started_at, finished_at) AS month_diff,
       format_datetime(started_at, 'yyyy-MM-dd') AS started_date
FROM jobs;
期望

新增 date_diff(part, start, end) 和可控的日期格式化函数,或明确拒绝格式化而仅提供标准化 ISO 输出。part 的支持范围应与 date_part/date_add 对齐并文档化。

验收标准
  • 规定 day/month/year 差是历法边界数、完整间隔数还是持续时间截断值。
  • 覆盖 UTC、local、DateTimeOffset、Unix 毫秒、DST 边界、NULL 和负差值。
  • 格式化必须使用固定可审计格式令牌,不受服务器区域设置隐式影响。
对 SqlSugar 的影响

可评估支持 DateDiffDateIsSameDayDateIsSameByTypeWeekOfYear 和日期 .ToString(format);在语义未落定前 provider 应继续拒绝。

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by tracing SonnetDB's existing date_part, date_add, and Unix time conversion SQL functions and their compatibility checks. Resolve the semantics and supported parts for date_diff and formatting before implementation; done means the documented UTC, local, offset, Unix-millisecond, DST, NULL, negative-difference, and locale-independent formatting cases are covered.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Active
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.