extract date from timestamp in postgreSQL

Question

we have a table of data that looks like:

|id|date1     |timestamp1         |
-----------------------------
|1 |2015-06-23|2015-06-23 16:02:00|
-----------------------------
|2 |2015-01-02|2015-01-02 11:32:00|

I tried to create a function and trigger which is not working, looks like this:

CREATE OR REPLACE FUNCTION insert_date1_trg_func()
RETURNS trigger AS
$BODY$
begin
update schema.table set
date1 = extract(date from new.timestamp1)
where id = new.id;
return null;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

trigger

CREATE TRIGGER insert_date1_trg_func()
AFTER INSERT or update
ON schema.table
FOR EACH ROW
EXECUTE PROCEDURE insert_date1_trg_func();

I am getting an error type date but expression double precision.

Previously posted as stackoverflow.com/q/31025100/398670

Craig Ringer
– Craig Ringer

2015-06-26 10:40:07 +00:00
Commented Jun 26, 2015 at 10:40 — Craig Ringer
– Craig Ringer, Commented Jun 26, 2015 at 10:40

Patrick · Accepted Answer · 2015-06-26 10:51:16Z

1

If you want to set "date1" in the update trigger, it should be like this:

CREATE OR REPLACE FUNCTION insert_date1_trg_func() RETURNS trigger AS $BODY$
BEGIN
  NEW.date1 = date_trunc('day', NEW.timestamp1)::date;
  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql STABLE;

Trigger:

CREATE TRIGGER insert_date1_trg_func()
BEFORE INSERT OR UPDATE ON schema.table
FOR EACH ROW EXECUTE PROCEDURE insert_date1_trg_func();

Note that the trigger should fire BEFORE the insert or update or the changes will not persist in the database.

edited Jun 26, 2015 at 10:51

answered Jun 26, 2015 at 10:29

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taz Over a year ago

@Patick: I am getting an error: timestamp unit "date not recognized". Thanks.

Collectives™ on Stack Overflow

extract date from timestamp in postgreSQL

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related