Re: Truncating 'now' to seconds

Nathan Schile <nate(at)shiftyeyes(dot)com> wrote:
>
> I am trying to run the following query:
>
> INSERT INTO public."Bug Status" ("Tracking Number", "Assigned Analyst",
> status, "Changed By", "Updated Date") VALUES
> (111111111, 'NS', 'Reported', 'NS', DATE_TRUNC('second', 'now'));
>
> I recieve the following error:
>
> ERROR: unterminated string
> CONTEXT: compile of PL/pgSQL function "update_status" near line 3
>
> I am basically wanting to truncate the timestamp to seconds, so that I
> don't get milliseconds. Does anyone know what I am doing wrong? Thanks

As with the date/time one I answered yesterday:
http://www.postgresql.org/docs/7.4/static/functions-datetime.html is
your friend.

select now();
now
-------------------------------
2004-05-21 16:39:43.843642-04
(1 row)

select date_trunc('second', now());
date_trunc
------------------------
2004-05-21 16:39:48-04
(1 row)

select date_trunc('second', current_timestamp);
date_trunc
------------------------
2004-05-21 16:39:55-04
(1 row)

select current_timestamp (0);
timestamptz
------------------------
2004-05-21 16:40:01-04
(1 row)

select localtimestamp (0);
timestamp
---------------------
2004-05-21 16:40:04
(1 row)

Btw: You could just define the timestamp column in your table to be
"timestamp (0)" and lose the fractional seconds that way, too.

Jim