strftime in sqlite convert to postgres

I came across a function in sqlite that I need to convert 开发者_如何学Cto postgres syntax. It's date(date, '-'||strftime('%w',date)||' days').

Can anyone help me convert this sqlite segment to postgres?

This SQLite:

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

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.

I think the equivalent for PostgreSQL would be:

d - extract(dow from d)::int

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.

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')

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')

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).

On the PostgreSQL side:

d - extract(dow from d)::int

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

and that's the same as:

d - interval '2 days'

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.

%w is the day of the week as a number from Sunday=0, same as Postgresql's DOW: SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); from http://www.postgresql.org/docs/current/static/functions-datetime.html

The string operations ought to be pretty simple, but I haven't done Postgres for about 10 years.

[after more research] The || operator, as I suspected, is string concatenation, from http://www.sqlite.org/lang_expr.html

[after installing sqlite and postgresql] the sqlite function is calculating the Sunday preceding the given date:

sqlite> select date('now', '-'||strftime('%w','now')||' days');
2011-08-21

mu is too short gave the final piece of the puzzle:

postgres=# select date 'now' - extract(dow from timestamp 'now')::int;
  ?column?  
------------
 2011-08-21

Given a format like "Mar 12 18:07:22",

PostgreSQL version using to_char function:

psql -c "SELECT to_char(time,'Mon DD HH24:MI:SS ') FROM mytable;" 

SQLite version using strftime function (with the help of awk):

sqlite3 'SELECT strftime("%s", time, "localtime") FROM mytable;' \
  |  awk '{print strftime("%b %e %H:%M:%S",$1) }' 

In the prior example,

• The printed format will be Mar 12 18:07:22
• The sqlite column time was a TEXT type as ISO8601 strings.
• The postgres column time was type time_stamp .