Monday, October 15, 2012

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;

No comments:

Post a Comment