sql

Ever needed to check if a date matches another within a certain range? If you need to trim to hours, minutes or seconds the date_trunc function in postgres does the job just fine. How about if you need to trunc to 10s? The following function should be of assistance: <pre lang="sql">create function date_trunc_x(timestamp with time zone, numeric) returns text AS $$ select substring(extract('hour' from $1)||':'||extract('minute' from $1)||':'||(extract('second' from $1)::int/$2) from 0 for 8) $$ LANGUAGE SQL; In can be used as in the following example:...

I have learned a lot about hibernate cache at the time I wrote the previous article on this subject and this brings also an interesting detail. You are using the query and second level cache. Here is an interesting sequence: 1. You execute a query which returns items of entity A. First time it takes some time. 2. You execute the query again, it takes much less time, you are very happy :)...

Assuming you have the following Oracle table: <pre lang="sql">create table icon(id number(19), icon blob); these are 2 methods I found for inserting the value of a file in the icon.icon column. Method 1 This is the most commonly found method: <pre lang="sql">CREATE OR REPLACE PROCEDURE load_icon (imagefile VARCHAR2) IS src_file BFILE; dst_file BLOB; icon_id NUMBER(19,0); BEGIN src_file := bfilename('TEMP', imagefile); -- insert a NULL record to lock INSERT INTO icon (ID, ICON) VALUES (icon_sequence....

After debuging for a few hours I’ve found the proper way of calling an Oracle function from hibernate and to use the return parameters. Here is the solution. The Oracle SQL function which returns -1 or 0: <pre lang="sql">create or replace function checkLastNPass (userId IN number, ... other params) RETURN SYS_REFCURSOR is st_cursor SYS_REFCURSOR; prevId number:= userId; ... other scope variables begin ... OPEN st_cursor FOR select -1 as retVal from DUAL; return st_cursor; ....

It seems that self-pings are bad for Google page ranking. Well, I have never spended too much time thinking about SEO but I find them a bit clutering and thus I’ve decided to remove them. There are some plugins which block new self-pings but none which deletes the existing ones. So here is a mysql command which does just that: Warning: don’t execute sql commands on your db if you don’t know anything about what sql is, it might ruin your base....

Q: Assume someone, for compatibility is storing java.util.Date values in Oracle as long values (number of milliseconds since the standard base time known as “the epoch”, namely January 1, 1970, 00:00:00 GMT), how to read these dates in sql? A: select TO_CHAR(TO_DATE('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(start_time/1000, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') from qrtz_triggers