Re: datestyle question - Mailing list pgsql-general

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.