Thread: Converting a timestamp to a time
I'm in the middle of moving a production database from 7.1 to 8.1 and have hit a slight problem. On the old system I've got a query including datastamp AS datastamp, date(datastamp ) as datadate, time(datastamp ) as datatime, status, -- etc. This is actually generated on the client to possibly include timezone correction. Obviously this has worked fine for a number of years on 7.1, but 8.1 is objecting to the time() cast. What is the correct (or even any :-) way of converting a timestamp into a time (without timezone etc.)? Any suggestions would be much appreciated. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk> schrieb:
> What is the correct (or even any :-) way of converting a timestamp into a time
> (without timezone etc.)?
You can CAST it:
test=# select now();
now
-------------------------------
2006-11-05 11:16:05.205235+01
(1 row)
test=# select now()::time;
now
----------------
11:16:18.22527
(1 row)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer schrieb: > > Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk> wrote: > > What is the correct (or even any :-) way of converting a timestamp into a > > time (without timezone etc.)? > > You can CAST it: > > test=# select now(); > now > ------------------------------- > 2006-11-05 11:16:05.205235+01 > (1 row) > > test=# select now()::time; > now > ---------------- > 11:16:18.22527 > (1 row) Thanks Andreas, I've ended up using CAST( ... AS TIME). I think the :: notation might be fragile in this instance because of the machine-generated SQL which gets a bit hairy in places. I've also had to replace INTERVAL() with CAST( ... INTERVAL) wherever it occurs and replace the result of a function with TIMESTAMP WITH TIME ZONE... hopefully that won't mess anything up, the server is aggresively GMT since we have to deal with several timezones simultaneously and it's the only way I could work it. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk> schrieb: > > test=# select now()::time; > > now > > ---------------- > > 11:16:18.22527 > > (1 row) > > Thanks Andreas, I've ended up using CAST( ... AS TIME). I think the :: notation > might be fragile in this instance because of the machine-generated SQL which Thats okay, because my version (the ::cast) is a PostgreSQL-feature, but the cast(... as ...) is more SQL-conform. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Andreas Kretschmer schrieb: > > Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk> wrote: > > > test=# select now()::time; > > > now > > > ---------------- > > > 11:16:18.22527 > > > (1 row) > > > > Thanks Andreas, I've ended up using CAST( ... AS TIME). I think the :: > > notation might be fragile in this instance because of the machine-generated > > SQL which > > Thats okay, because my version (the ::cast) is a PostgreSQL-feature, but > the cast(... as ...) is more SQL-conform. Thanks for that, feedback on "best practice" is always useful. I must admit that the only server I've used before pg was the "SOLID Server (using Bonzai Tree technology)", but they changed their licensing terms which made it impractical. I looked briefly at MySQL which in those days didn't support transactions, apart from that we decided that we didn't fancy waving the incredibly-tacky name in front of our customers :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]