Thread: Implementation of Date/Time Input Interpretation
Greetings all,<br /><br />I am currently implementing a script to import data into postgres. I would like to apply the algorithmto detect date and time values, outlined at <a href="http://developer.postgresql.org/pgdocs/postgres/datetime-input-rules.html">http://developer.postgresql.org/pgdocs/postgres/datetime-input-rules.html</a><br /><br/>However, I am unfamiliar (and somewhat intimidated) by the postgres source tree. Would any kind person be able topoint me to the source file(s) that implement the above algorithm?<br /><br />Thanks in advance,<br /><br />Francis Markham<br/>
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Francis Markham
Sent: Tuesday, June 22, 2010 7:13 PM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] Implementation of Date/Time Input Interpretation
Greetings all,
I am currently implementing a script to import data into postgres. I would like to apply the algorithm to detect date and time values, outlined at http://developer.postgresql.org/pgdocs/postgres/datetime-input-rules.html
However, I am unfamiliar (and somewhat intimidated) by the postgres source tree. Would any kind person be able to point me to the source file(s) that implement the above algorithm?
>>
You will find it under \src\backend\utils\adt\datetime.c
To import data into postgres, I guess that reading the date time routine is probably not what you want to do.
If you want to move the data in using a compiled program then use an ODBC driver. PostgreSQL comes with a free one. OLEDB is another sensible alternative. Or JDBC if you want to use Java.
If you want to bulk load lots of data at high speed, read up on the COPY command.
If you just want to insert some rows using SQL, then simply perform an INSERT using PSQL or some other interface of your choice.
What is it exactly that you are trying to accomplish?
<<
Thank you for your prompt reply. > What is it exactly that you are trying to accomplish? I want to be able to, from my own script, determine if postgres will be able to interpret a string as a date or time. If you can suggest a better way of accomplishing this beyond reimplementing your algorithm I would be happy to hear it! Cheers, Francis Markham On 23 June 2010 12:21, Dann Corbit <DCorbit@connx.com> wrote: > > From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Francis Markham > Sent: Tuesday, June 22, 2010 7:13 PM > To: pgsql-hackers@postgresql.org > Subject: [HACKERS] Implementation of Date/Time Input Interpretation > > > > Greetings all, > > I am currently implementing a script to import data into postgres. I would like to apply the algorithm to detect dateand time values, outlined at http://developer.postgresql.org/pgdocs/postgres/datetime-input-rules.html > > However, I am unfamiliar (and somewhat intimidated) by the postgres source tree. Would any kind person be able to pointme to the source file(s) that implement the above algorithm? > >> > > You will find it under \src\backend\utils\adt\datetime.c > > To import data into postgres, I guess that reading the date time routine is probably not what you want to do. > > If you want to move the data in using a compiled program then use an ODBC driver. PostgreSQL comes with a free one. OLEDBis another sensible alternative. Or JDBC if you want to use Java. > > If you want to bulk load lots of data at high speed, read up on the COPY command. > > If you just want to insert some rows using SQL, then simply perform an INSERT using PSQL or some other interface of yourchoice. > > What is it exactly that you are trying to accomplish? > > <<
Francis Markham wrote: > Thank you for your prompt reply. > > >> What is it exactly that you are trying to accomplish? >> > > I want to be able to, from my own script, determine if postgres will > be able to interpret a string as a date or time. If you can suggest a > better way of accomplishing this beyond reimplementing your algorithm > I would be happy to hear it! > > Call the appropriate input function in plpgsql and trap a data exception? These routines are going to be quite hard to mimic, I suspect. Getting postgres to do the work for you is probably a better way to go if you can. cheers andrew
> -----Original Message----- > From: Andrew Dunstan [mailto:andrew@dunslane.net] > Sent: Tuesday, June 22, 2010 7:47 PM > To: Francis Markham > Cc: Dann Corbit; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Implementation of Date/Time Input Interpretation > > > > Francis Markham wrote: > > Thank you for your prompt reply. > > > > > >> What is it exactly that you are trying to accomplish? > >> > > > > I want to be able to, from my own script, determine if postgres will > > be able to interpret a string as a date or time. If you can suggest > a > > better way of accomplishing this beyond reimplementing your algorithm > > I would be happy to hear it! > > > > > > Call the appropriate input function in plpgsql and trap a data > exception? These routines are going to be quite hard to mimic, I > suspect. Getting postgres to do the work for you is probably a better > way to go if you can. For the O.P.: This is the specification of the input format that is needed for date/time values: http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html See also: http://www.postgresql.org/docs/8.4/interactive/datetime-appendix.html If he wants to be able to simply validate date/time values before insertion, I would suggest a package like libmcal and pickout the file datetime.c, in particular: extern bool datevalid(int year,int mon,int mday); extern bool timevalid(int hour,int min,int sec); The PostgreSQL database routine has lots of fluff intended for interfacing with the database, etc. which makes a simplerapproach easier if validation is what is wanted. Of course date/time/calendar libraries are available in just aboutevery language.