extracting date from timestamp
От | Keith Worthington |
---|---|
Тема | extracting date from timestamp |
Дата | |
Msg-id | 20050124211923.M97003@narrowpathinc.com обсуждение исходный текст |
Ответы |
Re: extracting date from timestamp
Re: extracting date from timestamp |
Список | 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 по дате отправления: