New answers tagged timestamp
is your query1 and query2 are query from 1 table only? try to use this if you want to get query2 = query1 value + 5mins SELECT * FROM table_name where timestamp = query1 + INTERVAL 5 MINUTE;
something like select * from table where MOD(UNIX_TIMESTAMP(timestamp),300)) = 0; should do the job. But I am not sure if this is correct. The idea is to try to modulo divide the minute part evenly. Be aware, that this may cause in a full table scan
Since the seconds are from 0 (1970-01-01 00:00:00 UTC), you should look for every multiple of 60 SELECT * FROM mytable WHERE MOD(TimeStamp,60)=0; or if TimeStamp is indexed, you can do SELECT T.* FROM (SELECT TimeStamp FROM mytable WHERE MOD(TimeStamp,60)=0) M INNER JOIN mytable T USING (TimeStamp); Give it a Try !!! SUGGESTION #1 You should store ...
Try something like this SELECT UniqueKey, UNIX_TIMESTAMP(MyTimestamp) FROM MyTable WHERE UniqueKey IN ( SELECT MAX(UniqueKey) FROM MyTable GROUP BY MINUTE(MyTimeStamp)); See the SQLFiddle: http://sqlfiddle.com/#!2/abdcc/4 The above now does what you want. My first answer was a mess - I'll never forget SQLFiddle again! :-) To explain a ...
What you're looking for is generally known as a "greatest of group" solution. There are several patterns for this. A search of dba.SE or SO will show you some. The posted question and the SQL Fiddle have the table names swapped. This code is from the Fiddle: with NEWEST as ( select LOCATION_ID, MAX(LOCATION_TS) as MAX_TS from ...
For now While stuck with your unfortunate solution: CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time) That's right, AT TIME ZONE two times: The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times. The second instance converts ...
You may use to_char to get the time fields from a single function call: check (to_char(end_time at time zone 'UTC' at time zone 'US/Eastern','HH24:MI:SS') = '23:59:59') Seconds given by SS are not rounded up so that should be OK as an equivalent to floor
Try this. It maybe be faster, but still unreadable: alter table bananas add constraint ck_end_time_is_end_of_day check ( (end_time at time zone 'UTC' at time zone 'US/Eastern')::timestamp::date + interval '23:59:59' = date_trunc('second', end_time at time zone 'UTC' at time zone 'US/Eastern') ) ;
Top 50 recent answers are included