Обсуждение: extracting date from timestamp

Hi All,

I have timestamp information in a table.  I want to extract the date portion
for insertion into another table.

I tried this
DECLARE
   v_inventory_date DATE;
BEGIN
   SELECT CAST(extract(year from min(scan_timestamp)) || extract(month from
min(scan_timestamp)) || extract(day from min(scan_timestamp)) AS date)
     INTO v_inventory_date
     FROM inventory.tbl_scanner;

But I get an error that the format is invalid.  It seems that the extract is
returning a float.  Because the float is only a single digit I get the error.
 I am sure the day will give me the same error on the lower days.  How can I
extract the complete date or get a two digit day and/or month?

Here is my table definition and the data.
IPADB=# \d inventory.tbl_scanner;
                     Table "inventory.tbl_scanner"
     Column     |            Type             |       Modifiers
----------------+-----------------------------+------------------------
 scan_timestamp | timestamp without time zone | not null
 item_id        | character varying(20)       | not null
 quantity       | real                        | not null
 employee_id    | character varying(20)       | not null
 void           | boolean                     | not null default false
Indexes:
    "tbl_scanner_pkey" PRIMARY KEY, btree (scan_timestamp, item_id)
Foreign-key constraints:
    "tbl_scanner_fkey1" FOREIGN KEY (item_id) REFERENCES
peachtree.tbl_item(id) ON UPDATE CASCADE ON DELETE RESTRICT
    "tbl_scanner_fkey2" FOREIGN KEY (employee_id) REFERENCES
peachtree.tbl_employee(id) ON UPDATE CASCADE ON DELETE RESTRICT

IPADB=# SELECT * FROM inventory.tbl_scanner;
   scan_timestamp    | item_id | quantity | employee_id | void
---------------------+---------+----------+-------------+------
 2005-01-19 18:46:00 | 004     |       11 | 116         | t
 2005-01-19 18:45:00 | 004     |       10 | 116         | t
(2 rows)

Kind Regards,
Keith

Hi Keith,

> I tried this
[...]
>    SELECT CAST(extract(year from min(scan_timestamp)) || extract(month from
> min(scan_timestamp)) || extract(day from min(scan_timestamp)) AS date)
>      INTO v_inventory_date
>      FROM inventory.tbl_scanner;

You have the order for pl/pgsql SELECT INTO wrong (target variable(s)
first, then the value(s) you want to assign) and it is easier to get at
the information you want using the date_trunc function. Try

SELECT INTO
v_inventory_date
CAST(date_trunc('day', min(scan_timestamp)) AS date)
FROM inventory.tbl_scanner;

instead.

HTH,

Alex

"Keith Worthington" <keithw@narrowpathinc.com> writes:
> I have timestamp information in a table.  I want to extract the date portion
> for insertion into another table.

You're doing it the very very hard way.  Just cast the timestamp value
to date.

            regards, tom lane

On Tue, Jan 25, 2005 at 11:14:20AM +1100, Alexander Borkowski wrote:
>
> You have the order for pl/pgsql SELECT INTO wrong (target variable(s)
> first, then the value(s) you want to assign)

Either order is correct:

SELECT INTO var field FROM ...
SELECT field INTO var FROM ...

See the SELECT INTO documentation:

http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-SELECT-INTO

"The INTO clause can appear almost anywhere in the SELECT statement.
Customarily it is written either just after SELECT as shown above,
or just before FROM that is, either just before or just after the
list of select_expressions."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/