Thread: datestyle question
Hi, I have a file to import to postgresql that have an unusual date format. For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't know how to handle the DDMMYYYY dates. I tried and tried but I can't import those dates to postgresql. Any hint, other than editing file ? Regards, Diego.
On Sep 26, 2007, at 3:42 PM, Diego Gil wrote: > Hi, > > I have a file to import to postgresql that have an unusual date > format. > For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any > separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't > know how > to handle the DDMMYYYY dates. > > I tried and tried but I can't import those dates to postgresql. > > Any hint, other than editing file ? > > Regards, > Diego. Check out the following link that explains how Postgres parses date inputs: http://www.postgresql.org/docs/8.2/interactive/x71187.html, particularly section 1.c The simplest thing I can think of in your case would be to do a little bit of text processing on that field before inserting it. If you simply insert dashes between the different fields so that you have DD-MM-YYY then you can do SET DateStyle TO 'DMY'; and then your copy should be ok. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On 9/26/07, Diego Gil <diego@adminsa.com> wrote: > Hi, > > I have a file to import to postgresql that have an unusual date format. > For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any > separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't know how > to handle the DDMMYYYY dates. > > I tried and tried but I can't import those dates to postgresql. > > Any hint, other than editing file ? There are two approaches. One is to use something like sed or awk or perl or php to read the file and rearrange those bits to a format that makes sense to pgsql, or you can import that field into a text field, and use something like substring() in postgresql to update a new field that holds dates with the right numbers.
El mié, 26-09-2007 a las 17:22 -0500, Erik Jones escribió: > On Sep 26, 2007, at 3:42 PM, Diego Gil wrote: > > > Hi, > > > > I have a file to import to postgresql that have an unusual date > > format. > > For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any > > separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't > > know how > > to handle the DDMMYYYY dates. > > > > I tried and tried but I can't import those dates to postgresql. > > > > Any hint, other than editing file ? > > > > Regards, > > Diego. > > Check out the following link that explains how Postgres parses date > inputs: http://www.postgresql.org/docs/8.2/interactive/x71187.html, > particularly section 1.c > > The simplest thing I can think of in your case would be to do a > little bit of text processing on that field before inserting it. If > you simply insert dashes between the different fields so that you > have DD-MM-YYY then you can do > > SET DateStyle TO 'DMY'; > > and then your copy should be ok. > > Erik Jones > Thanks Erik. I was trying to avoid this, mainly because I will have to import several and different files. But having no other option, I will start to refreshing my awk knowledge. Regards, Diego.
El mié, 26-09-2007 a las 17:24 -0500, Scott Marlowe escribió: > On 9/26/07, Diego Gil <diego@adminsa.com> wrote: > > Hi, > > > > I have a file to import to postgresql that have an unusual date format. > > For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any > > separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't know how > > to handle the DDMMYYYY dates. > > > > I tried and tried but I can't import those dates to postgresql. > > > > Any hint, other than editing file ? > > There are two approaches. One is to use something like sed or awk or > perl or php to read the file and rearrange those bits to a format that > makes sense to pgsql, or you can import that field into a text field, > and use something like substring() in postgresql to update a new field > that holds dates with the right numbers. That is what I did on a previous file, sometime ago. Having now several date fields, I was trying to simplify the task, is possible. But it seems I will have no luck !. I will explore a little what Erik Jones suggested: inserting dashes with awk. Thanks, Diego.
On Sep 26, 2007, at 5:24 PM, Scott Marlowe wrote: > On 9/26/07, Diego Gil <diego@adminsa.com> wrote: >> Hi, >> >> I have a file to import to postgresql that have an unusual date >> format. >> For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any >> separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't >> know how >> to handle the DDMMYYYY dates. >> >> I tried and tried but I can't import those dates to postgresql. >> >> Any hint, other than editing file ? > > There are two approaches. One is to use something like sed or awk or > perl or php to read the file and rearrange those bits to a format that > makes sense to pgsql, or you can import that field into a text field, > and use something like substring() in postgresql to update a new field > that holds dates with the right numbers. You know, this type of request is fairly common and has got me thinking. If postgres had some kind of identity function a useful extension to the COPY syntax would be to allow the user to specify functions for each column that the imported data would be passed through. So, say you had the following table: CREATE TABLE test ( test_id serial primary key, test_val text, test_date timestamp); The COPY could be something like (with id being a built in identity function): COPY test (test_val, test_date) VALUES (id, regexp_replace(id, '(..) (..)(....)', '\\3-\\2-\\1') FROM '/somepath/somefile.csv' CSV; Alternatively, if the usage of id is obtuse, the particular field name could be used but I think that would probably work a little differently on the backend although not being involved with the backend I'm no expert. Just a random idea anyway. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
Diego Gil wrote: > Hi, > > I have a file to import to postgresql that have an unusual date format. > For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any > separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't know how > to handle the DDMMYYYY dates. You could try importing those fields in a text field in a temporary table and then convert them from there into your final tables using the to_date() function. If 20022007 really means 20 Jan instead of 20 Feb, try something like: insert into my_table (my_date_field) select to_date(my_date_text_field, 'DDMMYYYY') - interval '1 month' from my_temp_table; Regards, -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
El jue, 27-09-2007 a las 10:32 +0200, Alban Hertroys escribió:
> Diego Gil wrote:
> > Hi,
> >
> > I have a file to import to postgresql that have an unusual date format.
> > For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without any
> > separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't know how
> > to handle the DDMMYYYY dates.
>
> You could try importing those fields in a text field in a temporary
> table and then convert them from there into your final tables using the
> to_date() function.
>
> If 20022007 really means 20 Jan instead of 20 Feb, try something like:
>
No, it realy means 20 Feb. My mistake !.
> insert into my_table (my_date_field)
> select to_date(my_date_text_field, 'DDMMYYYY') - interval '1 month'
> from my_temp_table;
>
> Regards,
I finally ended coding a dirty C program to reverse the order of date
fields. Here is the code, in case anyone need it.
#define _GNU_SOURCE
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
int main(void)
{
FILE * fp, *f2, *f3;
char * line = NULL;
char * field = NULL;
size_t len = 0;
ssize_t read;
int fc = 1;
fp = fopen("trxs.exp", "r");
f3 = fopen("trxs.ok", "w");
if (fp == NULL)
exit(EXIT_FAILURE);
while (getline(&line, &len, fp) != -1)
{
fc = 1;
while ((field = strsep(&line, "\t")) != NULL)
{
if (fc > 1) fprintf(f3, "\t");
if (strlen(field) == 0) {
fprintf(f3, "\\N");
}
else if ( (fc == 9 || fc == 11 || fc == 12 || fc
== 14 || fc == 16)
&& strlen(field) >= 1)
{
fprintf(f3, "%c", field[4]);
fprintf(f3, "%c", field[5]);
fprintf(f3, "%c", field[6]);
fprintf(f3, "%c", field[7]);
fprintf(f3, "-");
fprintf(f3, "%c", field[2]);
fprintf(f3, "%c", field[3]);
fprintf(f3, "-");
fprintf(f3, "%c", field[0]);
fprintf(f3, "%c", field[1]);
}
else {
fprintf(f3, "%s", field);
}
fc++;
}
}
fclose(fp);
fclose(f3);
if (line)
free(line);
if (field)
free(field);
return EXIT_SUCCESS;
}
/* fc means "field count", only fields 9,11,12,14 and 16 are date
fields. */
Thanks for all suggestions.
Regards,
Diego.
On Oct 2, 2007, at 8:56 PM, Diego Gil wrote: > El jue, 27-09-2007 a las 10:32 +0200, Alban Hertroys escribió: >> Diego Gil wrote: >>> Hi, >>> >>> I have a file to import to postgresql that have an unusual date >>> format. >>> For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without >>> any >>> separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't >>> know how >>> to handle the DDMMYYYY dates. >> >> You could try importing those fields in a text field in a temporary >> table and then convert them from there into your final tables >> using the >> to_date() function. >> >> If 20022007 really means 20 Jan instead of 20 Feb, try something >> like: >> > No, it realy means 20 Feb. My mistake !. > > >> insert into my_table (my_date_field) >> select to_date(my_date_text_field, 'DDMMYYYY') - interval '1 month' >> from my_temp_table; >> >> Regards, > > I finally ended coding a dirty C program to reverse the order of date > fields. Here is the code, in case anyone need it. I'm glad you got something working. However, out of morbid curiousity I have to ask: why did you use C for that when you could have done it with at most a three line script or even one line directly from the shell? Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
El mié, 03-10-2007 a las 00:27 -0500, Erik Jones escribió: > On Oct 2, 2007, at 8:56 PM, Diego Gil wrote: > > > El jue, 27-09-2007 a las 10:32 +0200, Alban Hertroys escribió: > >> Diego Gil wrote: > >>> Hi, > >>> > >>> I have a file to import to postgresql that have an unusual date > >>> format. > >>> For example, Jan 20 2007 is 20022007, in DDMMYYYY format, without > >>> any > >>> separator. I know that a 20072002 (YYYYMMDD) is ok, but I don't > >>> know how > >>> to handle the DDMMYYYY dates. > >> > >> You could try importing those fields in a text field in a temporary > >> table and then convert them from there into your final tables > >> using the > >> to_date() function. > >> > >> If 20022007 really means 20 Jan instead of 20 Feb, try something > >> like: > >> > > No, it realy means 20 Feb. My mistake !. > > > > > >> insert into my_table (my_date_field) > >> select to_date(my_date_text_field, 'DDMMYYYY') - interval '1 month' > >> from my_temp_table; > >> > >> Regards, > > > > I finally ended coding a dirty C program to reverse the order of date > > fields. Here is the code, in case anyone need it. > > I'm glad you got something working. However, out of morbid > curiousity I have to ask: why did you use C for that when you could > have done it with at most a three line script or even one line > directly from the shell? I am a lot more used to work with C (25+ years doing it). In fact, it is easier to me writing it in C. That is all!. Could you post an equivalente script, so I can try it and learn?. "It is hard to teach new tricks to an old dog". Regards, Diego.