New answers tagged timestamp
One more option (and a variation): SELECT ts FROM (VALUES (now()::date + time '16:05'), ((now()::date - 1) + time '16:05') ) AS t (ts) WHERE ts < now() ORDER BY ts DESC LIMIT 1 ; SELECT x.ts FROM (VALUES (now()::date + time '16:05')) AS t (ts) , LATERAL ( SELECT ts UNION ALL SELECT ts - interval '1 day') AS x ...
The logic is similar to what @Daniel already posted, but this is a bit faster (twice as fast in my tests on pg 9.3): SELECT CASE WHEN now()::time > time '16:05' THEN now()::date + time '16:05' ELSE (now()::date - 1) + time '16:05' END AS ts; now() is the Postgres implementation of the SQL standard CURRENT_TIMESTAMP (which you ...
Start with today at 16:05 and if we're already past 16:05, subtract one day: select current_date + interval '16:05' - interval '1 day'*(case when current_time>'16:05' then 0 else 1 end) If a daylight saving time switch occurs during the interval, it should be taken care of implicitly.
Well, I started to write SQL to do the task. It got worse and worse. I needed several @variables and perhaps 3 levels of subqueries. So, I decided it was "impossible" in SQL, or at least very messy. Instead, I would recommend either david's suggestion of having daily prices, which would make AVG work easily (probably no @variables or subqueries), or ...
select extract(epoch from current_timestamp) * 1000; More details in the manual: http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
Your probably going to have to use group_concat function to aggregate by key so you can do math on those groups of values.
KISS Assuming daily means every day including weekends and holidays. Insert price for all items everyday Use AVG() for value Delete prices over 180 days.
Could I set the primary key to be the timestamp? Yes, the primary key can be any column but I think you'd be better off using an ID column for the primary key to ensure it's unique (which is technically impossible to gaurantee with a timestamp primary key). As ypercube suggests, a PK of (timestamp, id) and using milliseconds on the time value may be a ...
Top 50 recent answers are included