extracting date from timestamp

Поиск
Список
Период
Сортировка
От Keith Worthington
Тема extracting date from timestamp
Дата
Msg-id 20050124211923.M97003@narrowpathinc.com
обсуждение исходный текст
Ответы Re: extracting date from timestamp  (Alexander Borkowski <alexander.borkowski@abri.une.edu.au>)
Re: extracting date from timestamp  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-novice
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

В списке pgsql-novice по дате отправления:

Предыдущее
От: Wolfgang Keller
Дата:
Сообщение: MS SQL Server <-> PostgreSQL data migration _that_ _actually_ _works_?
Следующее
От: Alexander Borkowski
Дата:
Сообщение: Re: extracting date from timestamp