Thread: Syntax for converting double to a timestamp
I am trying to create a view based on this query 'select *, "timestamp"::timestamp from ccmanager_log' This is the error I get to below, how do I use the time zone syntax error: cannot cast tupe double precision to timestamp without time zone What is the right syntax? The column to be converted is also called timestamp F Church
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 - > or whatever you had in mind. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ >
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 > 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 > > > >
"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? > 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 // >
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... >I am trying to create a view based on this query > > 'select *, "timestamp"::timestamp from ccmanager_log' > > > This is the error I get to below, how do I use the time zone syntax > > error: cannot cast tupe double precision to timestamp without time zone > > What is the right syntax? > > > The column to be converted is also called timestamp > > F Church > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
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. > > > > ""Frank Church"" <voipfc@googlemail.com> wrote in message > news:84b7c6460609031557w1d5aab2t235ff2bdea2124ea@mail.gmail.com... > >I am trying to create a view based on this query > > > > 'select *, "timestamp"::timestamp from ccmanager_log' > > > > > > This is the error I get to below, how do I use the time zone syntax > > > > error: cannot cast tupe double precision to timestamp without time zone > > > > What is the right syntax? > > > > > > The column to be converted is also called timestamp > > > > F Church > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > choose an index scan if your joining column's datatypes do not > > match > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
-----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-----