Lists: | pgsql-general |
---|
From: | "Frank Church" <voipfc(at)googlemail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Syntax for converting double to a timestamp |
Date: | 2006-09-03 22:57:28 |
Message-ID: | 84b7c6460609031557w1d5aab2t235ff2bdea2124ea@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | "Frank Church" <voipfc(at)googlemail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-03 23:24:09 |
Message-ID: | 2E716010-C9B7-42E1-A691-8FA7344999F3@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | "Frank Church" <voipfc(at)googlemail(dot)com> |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-03 23:25:49 |
Message-ID: | 200609040125.50298.peter_e@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/
From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | "Frank Church" <voipfc(at)googlemail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-03 23:44:39 |
Message-ID: | FA949383-AEC5-42F0-A089-8DF73A097571@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "Frank Church" <voipfc(at)googlemail(dot)com> |
---|---|
To: | "Peter Eisentraut" <peter_e(at)gmx(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-04 00:11:14 |
Message-ID: | 84b7c6460609031711u2999a4dfsa49b4ee26d755a0e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 9/4/06, Peter Eisentraut <peter_e(at)gmx(dot)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/
>
From: | "Frank Church" <voipfc(at)googlemail(dot)com> |
---|---|
To: | "Michael Glaesemann" <grzm(at)seespotcode(dot)net> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-04 00:12:30 |
Message-ID: | 84b7c6460609031712u3dc041ccn86157c706783a44d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Peter's suggestion did not work.
On 9/4/06, Michael Glaesemann <grzm(at)seespotcode(dot)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
>
>
>
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Frank Church" <voipfc(at)googlemail(dot)com> |
Cc: | "Peter Eisentraut" <peter_e(at)gmx(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-04 00:49:34 |
Message-ID: | 2635.1157330974@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Frank Church" <voipfc(at)googlemail(dot)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
From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org, Frank Church <voipfc(at)googlemail(dot)com> |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-04 08:58:20 |
Message-ID: | 44FBEAAC.5010908@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)magproductions(dot)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 //
From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | Alban Hertroys <alban(at)magproductions(dot)nl> |
Cc: | Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-general(at)postgresql(dot)org, Frank Church <voipfc(at)googlemail(dot)com> |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-04 09:04:23 |
Message-ID: | 0C55E7C8-9C33-484E-AF42-5578612BD583@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
Cc: | Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-general(at)postgresql(dot)org, Frank Church <voipfc(at)googlemail(dot)com> |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-04 10:02:48 |
Message-ID: | 44FBF9C8.8080408@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)magproductions(dot)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 //
From: | "Frank Church" <voipfc(at)googlemail(dot)com> |
---|---|
To: | "Alban Hertroys" <alban(at)magproductions(dot)nl> |
Cc: | "Michael Glaesemann" <grzm(at)seespotcode(dot)net>, "Peter Eisentraut" <peter_e(at)gmx(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-04 18:10:57 |
Message-ID: | 84b7c6460609041110p4cd0ebfds263e0d87a702cb38@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 9/4/06, Alban Hertroys <alban(at)magproductions(dot)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(at)magproductions(dot)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 //
>
From: | "codeWarrior" <gpatnude(at)hotmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-05 16:12:48 |
Message-ID: | edk7ir$1v8n$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
It's generally considered bad form to use reserved words as column names....
""Frank Church"" <voipfc(at)googlemail(dot)com> wrote in message
news:84b7c6460609031557w1d5aab2t235ff2bdea2124ea(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
>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
>
From: | "Frank Church" <voipfc(at)googlemail(dot)com> |
---|---|
To: | codeWarrior <gpatnude(at)hotmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-05 21:30:21 |
Message-ID: | 84b7c6460609051430t469419e3h64dc8b25d5b03e0d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 9/5/06, codeWarrior <gpatnude(at)hotmail(dot)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(at)googlemail(dot)com> wrote in message
> news:84b7c6460609031557w1d5aab2t235ff2bdea2124ea(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
> >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
>
From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Syntax for converting double to a timestamp |
Date: | 2006-09-05 21:55:42 |
Message-ID: | 44FDF25E.7090603@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Frank Church wrote:
> On 9/5/06, codeWarrior <gpatnude(at)hotmail(dot)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-----