Syntax for converting double to a timestamp

On Sep 4, 2006, at 7:57 , Frank Church wrote:

I am trying to create a view based on this query

'select *, "timestamp"::timestamp from ccmanager_log'

<snip />

What is the right syntax?

Try this:

select *, to_timestamp("timestamp") from ccmanager_log

http://www.postgresql.org/docs/current/interactive/functions-
formatting.html#FUNCTIONS-FORMATTING-TABLE

Does it do what you want?

Michael Glaesemann
grzm seespotcode net

Frank Church wrote:

error: cannot cast tupe double precision to timestamp without time
zone

What is the right syntax?

It's not clear what the meaning of a double precision as a timestamp
would be. How about you make that explicit:

"timestamp" * interval '1 second' + timestamp '1900-01-01 00:00:00'

or whatever you had in mind.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Please reply to the list so that others may contribute to and learn
from the discussion.

On Sep 4, 2006, at 8:34 , Frank Church wrote:

ERROR: function to_timestamp(double precision) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.

The version I am on is 7.4 and that function is probably not there

That would it be it. You can follow Peter's suggestion or upgrade.

Michael Glaesemann
grzm seespotcode net

On 9/4/06, Peter Eisentraut <peter_e@gmx.net> wrote:

Frank Church wrote:

error: cannot cast tupe double precision to timestamp without time
zone

What is the right syntax?

It's not clear what the meaning of a double precision as a timestamp
would be. How about you make that explicit:

"timestamp" * interval '1 second' + timestamp '1900-01-01 00:00:00'

I tried the query in this form:

select *, "timestamp" * interval '1 second' + timestamp '1900-01-01
00:00:00' from ccmanager_log

This is the error message

ERROR: operator does not exist: interval + timestamp without time zone
HINT: No operator matches the given name and argument type(s). You
may need to add explicit type casts.

The version I am actually on is 7.4
- Hide quoted text -

Peter's suggestion did not work.

On 9/4/06, Michael Glaesemann <grzm@seespotcode.net> wrote:

Please reply to the list so that others may contribute to and learn
from the discussion.

I am now getting familiar with google mail

"Frank Church" <voipfc@googlemail.com> writes:

I tried the query in this form:

select *, "timestamp" * interval '1 second' + timestamp '1900-01-01
00:00:00' from ccmanager_log

Try timestamp plus interval, instead of the other way. (We did add the
operator in this direction in 8.0 though ... perhaps you should consider
an update sometime?)

regards, tom lane

Peter Eisentraut wrote:

Frank Church wrote:

error: cannot cast tupe double precision to timestamp without time
zone

What is the right syntax?

It's not clear what the meaning of a double precision as a timestamp
would be. How about you make that explicit:

"timestamp" * interval '1 second' + timestamp '1900-01-01 00:00:00'

There's also 'EPOCH', which is shorter and more explicit.

timestamp 'EPOCH" + "timestamp" * interval '1 second'

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

On Sep 4, 2006, at 17:58 , Alban Hertroys wrote:

Peter Eisentraut wrote:

It's not clear what the meaning of a double precision as a
timestamp would be. How about you make that explicit:
"timestamp" * interval '1 second' + timestamp '1900-01-01 00:00:00'

There's also 'EPOCH', which is shorter and more explicit.

timestamp 'EPOCH" + "timestamp" * interval '1 second'

Note that epoch does not mean 1900-01-01 00:00:00.

select timestamp 'epoch';
timestamp
---------------------
1970-01-01 00:00:00
(1 row)

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann wrote:

Note that epoch does not mean 1900-01-01 00:00:00.

Indeed! Where did this 1900 sneak in? Aren't timestamps usually based on
epoch?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

On 9/4/06, Alban Hertroys <alban@magproductions.nl> wrote:

Michael Glaesemann wrote:

Note that epoch does not mean 1900-01-01 00:00:00.

select *, timestamp 'EPOCH' + "timestamp" * interval '1 second' as
tstamp from ccmanager_log where id > 15400

select *, timestamp '1900-01-01 00:00:00' + "timestamp" * (interval
'1 second') from ccmanager_log where id > 15400

I tried both of these and the worked - but them timestamp '1900-01-01
00:00:00' gives dates that are 70 years of so it should be
'1970-01-01 00:00:00'

The sheer guruhood of PostgreSQL users is amazing.

Which topics in the manual discusses these issues in depth?

It's generally considered bad form to use reserved words as column names....

""Frank Church"" <voipfc@googlemail.com> wrote in message
news:84b7c6460609031557w1d5aab2t235ff2bdea2124ea@mail.gmail.com...

On 9/5/06, codeWarrior <gpatnude@hotmail.com> wrote:

It's generally considered bad form to use reserved words as column names....

I am aware of that - in this case the column names are chosen to
reflect exactly the names of the attributes of the event being
recorded.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Frank Church wrote:

On 9/5/06, codeWarrior <gpatnude@hotmail.com> wrote:

It's generally considered bad form to use reserved words as column
names....

I am aware of that - in this case the column names are chosen to
reflect exactly the names of the attributes of the event being
recorded.

Does the timestamp reflect an insert time, update, widget creation
date, etc, etc, etc? All these attributes modify TIMESTAMP.

For example, UPDATE_TIMESTAMP, CURRENT_TIMESTAMP,
TRANSACTION_TIMESTAMP, CREATION_TIMESTAMP, etc, etc, etc.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE/fJeS9HxQb37XmcRAjpRAJ0V0id/uxVZWE6hC45IZzlJzVKNHgCdEbbN
YoMAOqezJ77VAbEnpUNpF1U=
=jYb6
-----END PGP SIGNATURE-----