Re: [SQL] any additional date_time functions?
On Sat, Jun 17, 2006 at 13:08:20 -0700, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > I am working with the date_trunc() function with great success especially in > the group by clause > for aggregates. > > However, it is limited to returning "WHOLE" time units. i.e. years, months, > days, hours, minutes, > seconds. > > Are there any functions similar to date_trunc that can return variable > increments i.e.: > > 5, 10, or 15 minutes increments, > 3, 4, 6 hour increments, > 1, 2 weekly increments, > > I imagine that the returned values would have to either be the "floor" or > "ceiling" of the actual > time stamps. You might be able to extract the time since the epoch and divide it by the appropiate number of seconds (the length of your interval) and truncate the result. This might have unexpected results for you when you span daylight savings time changes. Another option is to not use timestamp, but rather just store an integer that represents some number of your intervals offset from an epoch. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] any additional date_time functions?
> > Are there any functions similar to date_trunc that can return variable > > increments i.e.: > > > > 5, 10, or 15 minutes increments, > > 3, 4, 6 hour increments, > > 1, 2 weekly increments, > You might be able to extract the time since the epoch and divide it by the > appropiate number of seconds (the length of your interval) and truncate > the result. This might have unexpected results for you when you span > daylight savings time changes. > > Another option is to not use timestamp, but rather just store an integer that > represents some number of your intervals offset from an epoch. Bruno, Thanks for the suggestions. I am going to "toy" around with them to see what I can get to work. Thanks for the help. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] concurrency problem
Looks good but you really shoud put your stmt.close() and conn.close() in a finally block so even if there is an error everything gets cleaned up properly. That syntax is for Java but the principle is the same for any programming language - always make sure you clean up your connections no matter what errors occur.
-Aaron BonoOn 6/17/06, Ash Grove <[EMAIL PROTECTED]> wrote:
Locks are released when the containing transactioncommits. There is no explicit "release."Instead of calling "begin" and "commit" as statements,I do something more like below. As Aaron mentioned,
this is JDBC, not SQL. Sorry people.try {...conn.setAutoCommit(false);//do the insert on the table that generates theprimary key via a sequencePreparedStatement pstmt =conn.prepareStatement
("my prepared statement");pstmt.executeUpdate();//your prepared statement above should do an//insert on a table that calls nextval().//Calling currval() below will guarantee that
you'll get//the value created by the insert statement//Check out the documentation on sequencefunctions//get the new primary keyString get_pkey = "{ ? = call currval('my_seq')
}";CallableStatement = conn.prepareCall(get_pkey);cstmt.registerOutParameter(1, Types.BIGINT);cstmt.execute();long new_pkey = cstmt.getLong(1);//do all of your updates/inserts on tables using
new_pkey as a foreign key//I like to do this in batchesStatement stmt = conn.createStatement();stmt.addBatch("insert into... )stmt.addBatch("update whatever set... )stmt.executeBatch
();conn.commit();stmt.close();conn.close();} catch(SQLException e1) {//do something with error 1if (conn != null) {try {conn.rollback();} catch(SQLException e2) {
//do something with error 2}}}
