I came across a function in sqlite that I need to convert to postgres syntax. It's date(date, '-'||strftime('%w',date)||' days').
Can anyone help me convert this sqlite segment to postgres?
Add a comment
|
3 Answers
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.
2 Comments
locoboy
can you take me through the logic of the sqlite statement and then the logic of the postgres? I'm having a difficult time following the reference.
mu is too short
@cfarm54: I added a bit of walk-through for you.
Given a datetime format like "Mar 12 18:07:22"
psql using to_char
PostgreSQL client using to_char function:
psql -c "SELECT to_char(time,'Mon DD HH24:MI:SS ') FROM mytable;"
In this example, the postgres column __time__ is type time_stamp.
sqlite3 piped to awk.
SQLite 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) }'
The printed format will be Mar 12 18:07:22. The sqlite column __time__ was a TEXT type as ISO8601 strings.
Comments
%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
1 Comment
locoboy
is there a replacement that you can suggest?