Tag Info

New answers tagged

1

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


3

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


2

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.


0

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


1

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


0

Your probably going to have to use group_concat function to aggregate by key so you can do math on those groups of values.


0

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.


0

Following John's advice; "try using NoSQL (like MongoDB) to leverage your JavaScript familiarity" I have been looking at MongoDB tutorials http://www.tutorialspoint.com/mongodb/mongodb_create_collection.htm and they have an example of a capped collection (table) that auto deletes old documents (rows) if the collection is 1000 documents long. This is ...


2

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