Tag Info

New answers tagged

0

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;


2

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


2

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


0

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


0

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


3

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


3

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


0

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


1

Will the ALTER TABLE command that changes only COLLATION of a column and nothing else increment values of a TIMESTAMP column on SQL Server 2008 R2 Std.? No it wont change/incement the values of timestamp column. altering a collation is specific to that column only. From BOL : The value in the timestamp column is updated every time a row containing ...



Top 50 recent answers are included