From: | "Keith Worthington" <keithw(at)narrowpathinc(dot)com> |
---|---|
To: | "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | extracting date from timestamp |
Date: | 2005-01-24 21:26:53 |
Message-ID: | 20050124211923.M97003@narrowpathinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Borkowski | 2005-01-25 00:14:20 | Re: extracting date from timestamp |
Previous Message | Wolfgang Keller | 2005-01-24 20:48:48 | MS SQL Server <-> PostgreSQL data migration _that_ _actually_ _works_? |