Re: Perl DBI and placeheld values

First, I've not managed to keep up with the traffic this week so I'm only just
getting around ot seeing some messages, plus an upstream network fault had me
offline all morning the other day and that always plays havoc with email
delivery from the lower priority servers.

However, I see nothing wrong with using timestamp to hold a timestamp. That's
what it's there for. However, I do see your argument that if it's not needed as
a timestamp but merely to hold a representation of a timestamp that someone has
entered then there's nothing lost storing it as a text type. That's fine if you
know that that is all you need from the column but what about if you want to
see how many widgets are expected to delivered by suppliers next month?

In short, a timestamp is a timestamp, it's not just there for recording
now() in a 'created at' or 'updated at' column.

Interesting point about epoch, which I assume your compatibility remark is
meaning, though. Given the result of time() system call should this always be
run through localtime()/gmtime() etc. to place into a textual representation
postgres understands before handing it over? I've seen on the list, and I
believe used myself, sql which gives the server time since epoch and let's it
determine the value of a timestamp from that.

BTW, thanks for bearing with me on this not so postgres specific question I
asked.

--
Nigel Andrews

On Thu, 30 Jan 2003, codeWarrior wrote:

> It occurs to me that you are sort of trying to bypass / defeat the purpose
> of "timestamp" -- I never try to create a home-grown timestamp -- Always
> use the postgreSQL CURRENT_TIME and NOW() for postgreSQL timestamps...
>
> ALSO: you probably already know but not all integers are integers -- There's
> int2, int4, int4, bigint, tinyint, longint, integer, serial, auto_increment,
> sequence, auto_number -- It depends on whose database you are using... The
> same thing applies for all "data types"... floats, reals, strings...
>
> As for "special casing" -- Who says Perl times are compatible with postgres
> times... Almost every time is system dependent -- Therefore -- you can
> either hard-code your perl scripts to match the OS or comply with
> postgreSQL's implementation (making them less portable) -- OR -- You can
> have yourself an epiphany and rethink your database strategy...
>
> your table might better be:
>
> create sequence "mytable_seq";
> create table mytable (
>
> id int4 default nextval "mytable_seq",
> thetime varchar(100) not null,
> create_dt timestamptz default 'NOW()'
>
> );
>
> $SQLSTMT = "INSERT INTO mytable (thetime) VALUES ('$thetime');";
> $SQL = $DBH->prepare($SQLSTMT);
> $result = $SQL->execute();
>
>
> ""Nigel J. Andrews"" <nandrews(at)investsystems(dot)co(dot)uk> wrote in message
> news:Pine(dot)LNX(dot)4(dot)21(dot)0301292217590(dot)2839-100000(at)ponder(dot)fairway2k(dot)co(dot)uk(dot)(dot)(dot)
> >
> > First off this is not really postgresql specific but it is driving me
> nuts.
> >
> > I thought I was using DBI to avoid the issues involved in constructing a
> SQL
> > query string using values held in variables. It turns out I'm not I'm
> using it
> > because it let's me write fetchrow_blah instead of some DB specific
> function
> > that does the samething, like the nice simple API of Pg that no one likes
> to
> > suggest people use.
> >
> > Anyway, back on to the subject. I'm a little stuck and I'm wondering how
> people
> > handle the situation where a variable can contain a value _or_ a function
> > call. For example:
> >
> > psql> create table mytab ( thetime timestamptz );
> >
> > perl:
> > $sth = $dbh->prepare('insert into mytab values ( ? )');
> > $sth->execute($thetime);
> >
> > where $thetime could hold 2003-01-29 13:45:06+00 _or_ current_timestamp.
> >
> > Obviously these are just going to be normal string scalars in perl and DBI
> is
> > just going to stick them in place as constant strings. Indeed it's
> difficult to
> > see how it could do otherwise without going to great lengths. Even if it
> did,
> > what then would it do if the column type was text? The trouble being is
> guess
> > what happens when you do:
> >
> > insert into mytab values ('current_timestamp');
> >
> > Yep, it doesn't like trying to insert an incorrect timestamp
> representation
> > into a timestamp field.
> >
> > So just how do others manage this situation without resorting to special
> casing
> > everything?
> >
> >
> > --
> > Nigel J. Andrews