Convert date time values to string in PostgreSQL

This is a view extracted from OpenERP.

CREATE OR REPLACE VIEW crm_claim_report
AS
SELECT min(c.id) AS id
, to_char(c.date, 'YYYY'::text) AS name
, to_char(c.date, 'MM'::text) AS month
, to_char(c.date, 'YYYY-MM-DD'::text) AS day
, to_char(c.date_closed, 'YYYY-MM-DD'::text) AS date_closed
, to_char(c.date_deadline::timestamp with time zone, 'YYYY-MM-DD'::text) AS date_deadline, c.state, c.user_id
, c.stage_id, c.section_id, c.partner_id, c.company_id, c.categ_id, count(*) AS nbr, c.priority
, c.type_action, date_trunc('day'::text, c.create_date) AS create_date
, avg(date_part('epoch'::text, c.date_closed - c.create_date)) / (3600 * 24)::double precision AS delay_close
, ( SELECT count(mailgate_message.id) AS count FROM mailgate_message
WHERE mailgate_message.model::text = 'crm.claim'::text AND mailgate_message.res_id = c.id AND mailgate_message.history = true) AS email
, ( SELECT avg(crm_case_stage.probability) AS avg FROM crm_case_stage
WHERE crm_case_stage.type::text = 'claim'::text AND crm_case_stage.id = c.stage_id) AS probability
, date_part('epoch'::text, c.date_deadline::timestamp without time zone - c.date_closed) / (3600 * 24)::double precision AS delay_expected
FROM crm_claim c
GROUP BY to_char(c.date, 'YYYY'::text), to_char(c.date, 'MM'::text), to_char(c.date, 'YYYY-MM-DD'::text), c.state, c.user_id, c.section_id
, c.stage_id, c.categ_id, c.partner_id, c.company_id, c.create_date, c.priority, c.type_action, c.date_deadline, c.date_closed, c.id;

My bat files to start/stop OpenERP services

Start OpenERP

@PATH=%WINDIR%\system32;%WINDIR%;%WINDIR%\System32\Wbem;.
@net start openerp-server-6.0
@net start openerp-web-6.0
@net start postgresql-9.0
pause

 

Stop OpenERP

@PATH=%WINDIR%\system32;%WINDIR%;%WINDIR%\System32\Wbem;.
@net stop openerp-server-6.0
@net stop openerp-web-6.0
@net stop postgresql-9.0
pause

Install OpenERP step by step

After installing server, web client, and GTK client

Open OpenERP Client, no database installed at this time

Create new database

Choose Extended for more options

For my research purpose, I installed everything

Follow the next steps in the wizard, you can finish the installation. Enjoy!

OpenERP: get configuration of hr.department

Screenshot of hr.departments.form in front-end.

You can find back-end text with the following query

SELECT arch 
FROM ir_ui_view
WHERE name = 'hr.department.form'

OpenERP: modify hr_department

Assume that you want to add a field description in hr_department table instead of using custom filed and also add this field into edit form of hr_department, follow these steps:

1. Add description field in hr_department:

ALTER TABLE hr_department
   ADD COLUMN description text;

2. Modify form view

OpenERP: hr_department scripts

Main fields: hr_department(id, name, parent_id, company_id, manager_id)
Screenshot:

CREATE TABLE hr_department
(
  id serial NOT NULL,
  create_uid integer,
  create_date timestamp without time zone,
  write_date timestamp without time zone,
  write_uid integer,
  note text, -- Note
  parent_id integer, -- Parent Department
  "name" character varying(64) NOT NULL, -- Department Name
  company_id integer, -- Company
  manager_id integer, -- Manager
  CONSTRAINT hr_department_pkey PRIMARY KEY (id),
  CONSTRAINT hr_department_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES res_company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT hr_department_create_uid_fkey FOREIGN KEY (create_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT hr_department_manager_id_fkey FOREIGN KEY (manager_id)
      REFERENCES hr_employee (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT hr_department_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES hr_department (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT hr_department_write_uid_fkey FOREIGN KEY (write_uid)
      REFERENCES res_users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE hr_department OWNER TO "admin";
COMMENT ON TABLE hr_department IS 'hr.department';
COMMENT ON COLUMN hr_department.note IS 'Note';
COMMENT ON COLUMN hr_department.parent_id IS 'Parent Department';
COMMENT ON COLUMN hr_department."name" IS 'Department Name';
COMMENT ON COLUMN hr_department.company_id IS 'Company';
COMMENT ON COLUMN hr_department.manager_id IS 'Manager';


CREATE INDEX hr_department_company_id_index
  ON hr_department
  USING btree
  (company_id);

CREATE INDEX hr_department_parent_id_index
  ON hr_department
  USING btree
  (parent_id);

My OpenERP services setting

1. OpenERP Server 6.0 (Automatic)
2. OpenERP Web 6.0 (Automatic)
3. postgresql-9.0 - PostgreSQL Server 9.0 (Automatic)