sqlite中的strftime转换为postgres [英] strftime in sqlite convert to postgres

问题描述

我在sqlite中遇到了一个需要转换为postgres语法的函数。这是 date（date，'-'|| strftime（'％w'，date）||'day's）。

I came across a function in sqlite that I need to convert to postgres syntax. It's date(date, '-'||strftime('%w',date)||' days').

有人可以帮助我将此sqlite段转换为postgres吗？

Can anyone help me convert this sqlite segment to postgres?

推荐答案

此SQLite：

date(date, '-' || strftime('%w', date) || ' days')

是AFAIK，减去周中的天数（即，0代表星期日，1代表星期一，...） date ，然后将结果转换回 date ；有关详细信息，请参见日期函数参考。

Is, AFAIK, subtracting the day-in-the-week number days (i.e. 0 for Sunday, 1 for Monday, ...) from date and then converting the result back to a date; see the date function reference for details.

我认为PostgreSQL的等效项是：

I think the equivalent for PostgreSQL would be:

d - extract(dow from d)::int

其中 d 是您的约会；从日期减去整数将减去该天数。如果 d 是一个时间戳，则可能需要添加一些强制转换。有 date_trunc（'周， d） ，但是从星期一开始就开始编号，因此您会因此而烦恼。

where d is your date; subtracting an integer from a date subtracts that number of days. If d is a timestamp, then you might need to add some casting. There is date_trunc('week', 'd') as well but that starts numbering the days from Monday so you'd be off by one with that.

以下是SQLite的快速分解，其中 date 变量替换为 d 以避免与 date（）函数混淆：

Here's a quick breakdown of the SQLite with the date variable replaced by d to avoid confusion with the date() function:

date(d, '-' || strftime('%w', d) || ' days')

首先， || 是标准的SQL字符串连接运算符。 strftime 函数是通用日期和来自 POSIX 的时间格式化程序； ％w 格式说明符的意思是星期几为数字，星期日为零日；因此 strftime 调用会给您0（星期日），1（星期一），依此类推，直到星期六（6）。如果 d 是星期二，则 strftime 调用将产生2，而整个结果为：

First of all, || is the standard SQL string concatenation operator. The strftime function is a general purpose date and time formatter that comes from POSIX; the %w format specifier means "day of the week as a number with Sunday being day zero"; so the strftime call gives you 0 for Sunday, 1 for Monday, and so on up to 6 for Saturday. If d is a Tuesday, then the strftime call will yield 2 and the whole thing ends up as:

date(d, '-2 days')

SQLite的修饰符 date 函数具有多种形式，但'-2 days'的含义与您想的一样：从 d减去两天。总体结果是，您将 d 截断到了一周（其中周日被认为是一周的第一天）。

The modifiers for the SQLite date function have various forms but '-2 days' means just what you'd think: subtract two days from d. The overall result is that you get d truncated to the week (where Sunday is considered to be the first day of the week).

在PostgreSQL上：

On the PostgreSQL side:

d - extract(dow from d)::int

我们可以从 提取 ； 提取用于提取日期或时间的特定部分， dow 表示星期几，星期日是零日。听起来有点熟？然后 :: int 将DOW数转换为整数，这是必需的，因为DOW实际上是双精度值，并且没有定义用于从中减去双精度数的运算符PostgreSQL中的日期；强制转换也可以以 cast（x int）的标准格式编写。当您从PostgreSQL中的日期中减去一个整数时，就减去了很多天。您可以说类似-间隔 3天 这样的方式来表达得更明确，但是在这种情况下，这只会增加更多的噪音，因此我选择了简单性。如果是星期二，则我们的PostgreSQL版本如下所示：

we can start with extract; extract is used to extract specific parts of a date or time and dow means "day of the week as a number with Sunday being day zero". Sound familiar? Then the ::int casts the DOW number to an integer and is needed because the DOW actually comes out as a double precision value and there is no operator defined for subtracting a double from a date in PostgreSQL; the cast can also be written in the standard form as cast(x as int). When you subtract an integer from a date in PostgreSQL, you subtract that many days; you can be more explicit by saying things like - interval '3 days' but that would just add more noise in this case so I opted for simplicity. If it is Tuesday, then our PostgreSQL version looks like:

d - 2

就是这样：

d - interval '2 days'

减去后，我们将在周日回来。还有 date_trunc 在PostgreSQL中，但是会截断到星期一而不是星期天。

And after the subtraction we'd be back on Sunday. There's also date_trunc in PostgreSQL but that would truncate to Monday not Sunday.

这篇关于sqlite中的strftime转换为postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！