Обсуждение: Import CSV with Dates & Times
Hi, I have a problem importing some data. I have created a table with a column which is called scheduled_depart_time and has the data type 'TIMESTAMP'. When I try to import data from a CSV the data in that column has values such as '17/11/2011 16:54:03'. I get an error of the below: ERROR: date/time field value out of range: "17/11/2011 15:17:00" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY ibus_17_11_2011, line 1, column scheduled_depart_time: "17/11/2011 15:17:00" Any sugestions please? Thanks James
On 4 May 2012 12:38, James David Smith <james.david.smith@gmail.com> wrote: > Any sugestions please? Use pgloader -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Simon, Thanks for this. I don' t have pgloader installed and a quick search seems to suggest it's a Linux tool? I'm working in Windows unfortunately. Maybe a bit more info would be good... Here is my table: create table ibus_17_11_2011( route INTEGER, scheduled_depart_time TIMESTAMP ) Here is my copy command: COPY ibus_17_11_2011 from 'C:/Program Files (x86)/PostgreSQL/8.4/data/ibus_17_11_2011_v2.csv' DELIMITERS ',' CSV; My date looks like this: 2 , 17/11/2011 17:08:35 3 , 17/11/2011 17:08:56 Thanks James On 4 May 2012 12:47, Simon Riggs <simon@2ndquadrant.com> wrote: > On 4 May 2012 12:38, James David Smith <james.david.smith@gmail.com> wrote: > >> Any sugestions please? > > Use pgloader > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
Just to report that I've realised what the problem was. PostgreSQL was expecting the data in an american format of MM/DD/YYYY rather than the UK version which is DD/MM/YYYY . On 4 May 2012 13:31, James David Smith <james.david.smith@gmail.com> wrote: > Simon, > > Thanks for this. I don' t have pgloader installed and a quick search > seems to suggest it's a Linux tool? I'm working in Windows > unfortunately. Maybe a bit more info would be good... > > Here is my table: > > create table ibus_17_11_2011( > route INTEGER, > scheduled_depart_time TIMESTAMP > ) > > Here is my copy command: > > COPY ibus_17_11_2011 from 'C:/Program Files > (x86)/PostgreSQL/8.4/data/ibus_17_11_2011_v2.csv' DELIMITERS ',' CSV; > > My date looks like this: > > 2 , 17/11/2011 17:08:35 > 3 , 17/11/2011 17:08:56 > > Thanks > > James > > > > On 4 May 2012 12:47, Simon Riggs <simon@2ndquadrant.com> wrote: >> On 4 May 2012 12:38, James David Smith <james.david.smith@gmail.com> wrote: >> >>> Any sugestions please? >> >> Use pgloader >> >> -- >> Simon Riggs http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Training & Services
Have you considered this:
On 5/4/2012 6:38 AM, James David Smith wrote:
to_timestamp('201024June10:12am', 'YYYYDDFMMonthHH12:MIam')
The 'to_timestamp' function - allows for a format string.
On 5/4/2012 6:38 AM, James David Smith wrote:
Just to report that I've realised what the problem was. PostgreSQL was expecting the data in an american format of MM/DD/YYYY rather than the UK version which is DD/MM/YYYY . On 4 May 2012 13:31, James David Smith <james.david.smith@gmail.com> wrote:Simon, Thanks for this. I don' t have pgloader installed and a quick search seems to suggest it's a Linux tool? I'm working in Windows unfortunately. Maybe a bit more info would be good... Here is my table: create table ibus_17_11_2011( route INTEGER, scheduled_depart_time TIMESTAMP ) Here is my copy command: COPY ibus_17_11_2011 from 'C:/Program Files (x86)/PostgreSQL/8.4/data/ibus_17_11_2011_v2.csv' DELIMITERS ',' CSV; My date looks like this: 2 , 17/11/2011 17:08:35 3 , 17/11/2011 17:08:56 Thanks James On 4 May 2012 12:47, Simon Riggs <simon@2ndquadrant.com> wrote:On 4 May 2012 12:38, James David Smith <james.david.smith@gmail.com> wrote:Any sugestions please?Use pgloader -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
James David Smith <james.david.smith@gmail.com> writes:
> ERROR: date/time field value out of range: "17/11/2011 15:17:00"
> HINT: Perhaps you need a different "datestyle" setting.
> CONTEXT: COPY ibus_17_11_2011, line 1, column scheduled_depart_time:
> "17/11/2011 15:17:00"
> Any sugestions please?
As the hint suggests, you probably need to adjust the datestyle setting
(to "dmy", looks like).
regards, tom lane