[SQL] problem converting strings to timestamps with time zone
Hi,
I have a table with varchar and timestamp with time zone columns, among
others. Insertions into this table are usually done by a java program;
and there are many tens of thousands of records in the table.
Recently, after changes in the java software, many timestamps values
inadvertently ended up in one of the varchar columns. Rather than
writing some more java to rectify the situation, I was hoping it could
be done with one update statement.
However I'm having no luck constructing such a statement; I can't find a
way to include the timezones in the update. It seems like this would be
a rather common situation, but I've found no answers in the
documentation nor the archives.
If anyone knows how to do this, I would be most grateful.
Here is a sandbox example of what I mean.
CREATE TABLE test_0
(
string_value varchar(2047),
timestamp_value timestamp with time zone
);
insert into test_0
(string_value)
values
('2010-03-12 17:06:21-0800'),
('2009-08-14 16:47:40+0500'),
('2010-03-22 22:45:59-0400');
As expected, select * from test_0; , produces the following:
string_valuetimestamp_value
2010-03-12 17:06:21-0800
2009-08-14 16:47:40+0500
2010-03-22 22:45:59-0400
I would like to parse the strings into their equivalent timestamps
and put them in the timestamp_value column.
My attempt, so far, to update the table:
update value
set
timestamp_value =
to_timestamp(string_value, '-MM-DD HH24:MI:SS-');
^
|
|
The X's just mark where I would like to specify a time zone. But
apprently to_timestamp has no way of inputting time zones. Does anyone
know of another way?
Thanks,
Edward Ross
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem converting strings to timestamps with time zone
Tom Lane wrote:
Edward Ross writes:
Here is a sandbox example of what I mean.
CREATE TABLE test_0
(
string_value varchar(2047),
timestamp_value timestamp with time zone
);
insert into test_0
(string_value)
values
('2010-03-12 17:06:21-0800'),
('2009-08-14 16:47:40+0500'),
('2010-03-22 22:45:59-0400');
As expected, select * from test_0; , produces the following:
string_valuetimestamp_value
2010-03-12 17:06:21-0800
2009-08-14 16:47:40+0500
2010-03-22 22:45:59-0400
I would like to parse the strings into their equivalent timestamps
and put them in the timestamp_value column.
My attempt, so far, to update the table:
update value
set
timestamp_value =
to_timestamp(string_value, '-MM-DD HH24:MI:SS-');
Forget to_timestamp; just cast the string to timestamptz. The
regular timestamp input converter will handle that format fine.
regards, tom lane
That worked great.
Thanks very much,
Edward Ross
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Re: Pregunta acerca de la función COPY de POSTGRES
Estas tratando de realizar esta operacion desde un stored procedure? Si es asi, investiga como se hace para concatenar variables tipo character. Creo que simplemente poner tu variable en un comando no es suficiente. Saludos, Edward Ross On 04/11/2012 10:42 AM, yeison garcia wrote: Buenas tardes. Mi nombre es Yeison Garcia Escobar. Estoy dando mis primeros pasos en postgres y tengo la tarea de investigar las cargas masivas. Estuve buscando y encontre la función COPY FROM, la cual es excelente. Al usar copy _edb_scheduler.persona from 'C:/persona.txt' using delimiters ',' ; funciona correctamente. Sin embargo, yo necesito que la ruta sea guardada en una variable. Ejemplo: Tengo la variable ruta character; ruta := 'C:/persona.txt'; al usar copy _edb_scheduler.persona from ruta using delimiters ',' ; me sale un error indicando un error de sintaxis indicando que la variable ruta no debe estar ahí. La pregunta es: ¿Como puedo hacer para que la función COPY tome la información que esta en una variable? Muchas gracias por la ayuda que me puedan brindar.
