Re: Timestamp input + copy

Kevin Bartz wrote:
> I have a flat file with a column with dates formatted like this:
>
> 2004-04-15 18:04:26 PM
>
> It's a bit strange, I know, but I didn't create the file. My idea of
> Postgres's proper behavior would be to load this date as a military
> time (and ignore the "PM"). MS SQL Server behaves in this way.

I couldn't disagree more that it would be correct behavior to ignore the PM
and treat it as 24-hour time. It's one of the most important features of a
database that when you give is bad data, it responds with an error message
rather than trying to guess at what you mean. Why are you using a database,
if not to ensure that you can trust your data; and how can you trust data that
comes from an ambiguous source?

I'm sorry to hear that SQL Server accepts this input without complaint. It's
caused me to lose confidence in that product.

> What can I do about
> this? Can I possibly specify a time format (similar to the
> 'YYYY-MM-DD HH24:MI:SS' I might pass to to_timestamp) at load time?

If you know that the time is in 24-hour form and want to ignore the AM or PM
specifier, then you can certainly run it by a processor written in pretty much
any programming language that will fix it. In UNIX sed, it looks like this
(all on one line):

cat data.txt | sed 's([0-9]{2,4}\-[0-9]{1,2}\-[0-9]{1,2}
[0-9]{1,2}\:[0-9]{1,2}:[0-9]{1,2}) ((AM|PM))?/\1/g' > data.txt.fixed

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation