Postgres Date Add function

arun.p.johny (Arun P Johny) — Tue, 27 May 2008 12:55:44 GMT

This function adds a year/month/day to a given date



CREATE OR REPLACE FUNCTION dateadd(diffType Character Varying(15), incrementValue int, inputDate timestamp) RETURNS timestamp AS $$

DECLARE

   YEAR_CONST Char(15) := 'year';

   MONTH_CONST Char(15) := 'month';

   DAY_CONST Char(15) := 'day';



   dateTemp Date;

   intervals interval;

BEGIN

   IF lower($1) = lower(YEAR_CONST) THEN

       select cast(cast(incrementvalue as character varying) || ' year' as interval) into intervals;

   ELSEIF lower($1) = lower(MONTH_CONST) THEN

       select cast(cast(incrementvalue as character varying) || ' months' as interval) into intervals;

   ELSEIF lower($1) = lower(DAY_CONST) THEN

       select cast(cast(incrementvalue as character varying) || ' day' as interval) into intervals;

   END IF;



   dateTemp:= inputdate + intervals;



   RETURN dateTemp;

END;

$$ LANGUAGE plpgsql;

Dump postgres production data into development database

nicwilliams (Dr Nic Williams) — Sun, 06 May 2007 09:00:17 GMT

When bug requests come in, its great to grab a copy of the current production (or system test env) data and sync it into your development database.

Here's a capistrano recipe for postgresql:



desc "Dumps target database into development db"

task :sync_db do

  env   = ENV['RAILS_ENV'] || ENV['DB'] || 'production'

  file  = "#{application}.sql.bz2"

  remote_file = "#{shared}/log/#{file}"

  run "pg_dump --clean --no-owner --no-privileges -U#{db_user} -h#{db_host} #{db_name}_#{env} | bzip2 > #{file}" do |ch, stream, out|

    ch.send_data "#{db_password}\n" if out =~ /^Password:/

    puts out

  end

  puts rsync = "rsync #{user}@#{domain}:#{file} tmp"

  `#{rsync}`

  puts depackage = "bzcat tmp/#{file} | psql #{local_db_dev}"

  `#{depackage}`

end

DZone Snippets: postgres code

Postgres Date Add function

Dump postgres production data into development database