On 2009-07-18, Gianvito Pio wrote:
> This is a multi-part message in MIME format.
>
> --=_NextPart_000_0017_01CA0791.49E30EB0
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> Hi all,
> how can I define a column of timestamp with timezon
Hi,
i think:
create. ...(
myCol timestamp with timezone default now()
)
the function now() returns a timestamp with timezone (
http://www.postgresql.org/docs/8.4/static/functions-datetime.html)
i currently have no db to test it - but it should work.
jasmin
2009/7/18 Gianvito Pio
> Hi all,
Hi all,
how can I define a column of timestamp with timezone type...with a default
value?
Thanks
Thank you, very much. I pass through extract(epoch from x).
On Wednesday 12 March 2008 19:58:07 Aurynn Shaw wrote:
> Valter Douglas Lisbôa Jr. wrote:
> > Hello all.
> >
> > I working with postgreSQL for a couple of months, and now I trying to
> > convert timestamp columns in epoch and epoch to ti
Valter Douglas Lisbôa Jr. wrote:
Hello all.
I working with postgreSQL for a couple of months, and now I trying to convert
timestamp columns in epoch and epoch to timestamp columns. So far I could
reach, all SQL I find by 'googling' returns an error.
SELECT extract (epoch from your_time_field
Hello all.
I working with postgreSQL for a couple of months, and now I trying to convert
timestamp columns in epoch and epoch to timestamp columns. So far I could
reach, all SQL I find by 'googling' returns an error.
Where can I find some documentation with this convertions fo 8.2.x branch? I
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> Well, you can't get better than microsecond precision with timestamps
> in Postgres. And the only way you can rely on that level of precision
> is to compile with --enable-integer-datetimes.
There is more precision in there, but the output routi
If I reduce the precision to 3 for the timestamps it appears to work
although it makes me nervous.
With float timestamps, you're fooling yourself if you think those
numbers past the decimal are reliable.
Should I just recompile to use integer datetimes? I would like to
have at least micros
On Jan 22, 2008, at 3:05 , Stuart Brooks wrote:
If I reduce the precision to 3 for the timestamps it appears to
work although it makes me nervous.
With float timestamps, you're fooling yourself if you think those
numbers past the decimal are reliable.
Should I just recompile to use integ
I have a problem in comparing a TIMESTAMP field with a timestamp
literal. I presume it is to do with the floating point representation of
the timestamp but I was wondering if there is an easy work around
without having to recompile postgres to use integer datetimes.
Basically if I issue a "SEL
On 8/11/06, Aaron Bono <[EMAIL PROTECTED]> wrote:
I put a create_dt and modify_dt column on every table and set the default to
now(). Then I use this trigger:
CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF
opaque AS
'
BEGIN
-- if a trigger insert or update oper
On 10 Aug 2006 06:28:30 -0700, Andrew Hammond <[EMAIL PROTECTED]> wrote:
Tomski wrote:> Hello!> As many of you know, SQL Server (2000) has peculiar data type "timestamp"> which is not SQL standard timestamp. In fact it is "rowversion" type. It> makes tha field to be updated with current timestamp w
Tomski wrote:
> Hello!
> As many of you know, SQL Server (2000) has peculiar data type "timestamp"
> which is not SQL standard timestamp. In fact it is "rowversion" type. It
> makes tha field to be updated with current timestamp when row is updated or
> inserted.
> Is there any similiar functionali
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> -> Bitmap Heap Scan on page_schedule ps2 (cost=573.65..2822.86
> rows=91614 width=16) (actual time=149.788..505.438 rows=94798 loops=1)
> Recheck Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17
> 08:09:18'::timestamp wit
On Sun, 21 May 2006, Tom Lane wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
I'm trying to figure out some way to speed up the following query:
select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
from page_schedule ps2
where ps2.start_time at time zone 'MST7
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> I'm trying to figure out some way to speed up the following query:
>select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
> from page_schedule ps2
> where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
> group
On Sun, 21 May 2006, Michael Glaesemann wrote:
On May 21, 2006, at 10:42 , Marc G. Fournier wrote:
-> Seq Scan on page_schedule ps2 (cost=0.00..2364.95 rows=33110
width=16) (actual time=0.021..623.363 rows=94798 loops=1)
I don't know about rewriting the query, but it appears your statis
On May 21, 2006, at 10:42 , Marc G. Fournier wrote:
-> Seq Scan on page_schedule ps2 (cost=0.00..2364.95
rows=33110 width=16) (actual time=0.021..623.363 rows=94798 loops=1)
I don't know about rewriting the query, but it appears your
statistics are a little out of date (e.g., rows exp
I'm trying to figure out some way to speed up the following query:
select ps2.page_id, ps2.template_component_id, max(ps2.start_time)
from page_schedule ps2
where ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
group by ps2.page_id, ps2.template_component_id
When run throug
WHERE event_type < NOW() - interval '30 days';
"Brian Doyle" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I have a table like:
>
> events
> visitor_uid varchar(32)
> event_type varchar(32)
> event_type timestamp
>
> I would like to select events from the table that are olde
I have a table like:
events
visitor_uid varchar(32)
event_type varchar(32)
event_type timestamp
I would like to select events from the table that are older than 30 days
from right now. How would I do that in one query? Thanks.
---(end of broadcast)---
T E Schmitz wrote:
>
> Hello,
>
> I *detest* British summertime. This year it took me two days to adjust.
> Now I am realizing that my program might need some adjusting too:
>
> Joking aside, I need some advice regarding TIMESTAMP colums and I can't
> quite get my head round this at the moment:
Hello,
I *detest* British summertime. This year it took me two days to adjust.
Now I am realizing that my program might need some adjusting too:
Joking aside, I need some advice regarding TIMESTAMP colums and I can't
quite get my head round this at the moment:
I created a table TRANSAKTION with a
thanks, christoph. i did go ahead and retool all the tables, but glad
to know about the casting option.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
"[EMAIL PROTECTED]" wrote:
>
> I have a database with several tables that use timestamp without time
> zone type. I upgraded from an older pgsql and have code that does not
> expect the precision in the select output. Is there a way I can get the
> effect of zero precision without modifying all th
I have a database with several tables that use timestamp without time
zone type. I upgraded from an older pgsql and have code that does not
expect the precision in the select output. Is there a way I can get the
effect of zero precision without modifying all the tables?
The docs say it usees "defa
O Bruno Wolff III έγραψε στις Feb 22, 2005 :
> On Mon, Feb 21, 2005 at 16:16:04 +0200,
> Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
> >
> > Since pgsql always converts a timestamptz to UTC, we have lost
> > the information of the Sender's local timezone.
> >
> > Should i go with a separete
On Mon, Feb 21, 2005 at 16:16:04 +0200,
Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
>
> Since pgsql always converts a timestamptz to UTC, we have lost
> the information of the Sender's local timezone.
>
> Should i go with a separete date and timetz ?
Someone else gave you a recommended solut
O Andrew - Supernews έγραψε στις Feb 21, 2005 :
> On 2005-02-21, Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
> > Consider a schema designed to store internet mail.
> >
> > Since pgsql always converts a timestamptz to UTC, we have lost
> > the information of the Sender's local timezone.
> >
> > S
On 2005-02-21, Achilleus Mantzios <[EMAIL PROTECTED]> wrote:
> Consider a schema designed to store internet mail.
>
> Since pgsql always converts a timestamptz to UTC, we have lost
> the information of the Sender's local timezone.
>
> Should i go with a separete date and timetz ?
No. Consider inst
AFAIK, the input for a timestamptz is converted and stored as UTC.
And outputing a timezonetz value converts the internally stored UTC
value to the current locale's timezone.
So there is not a way to actually store the original TZ itself,
whereas the timetz type clearly does that.
Consider a sch
On Thu, Jan 20, 2005 at 11:41:41AM +0200, Andrei Bintintan wrote:
> >Another possibility would be to create a functional index on datetime:
> >
> >CREATE INDEX user_action_date_idx ON user_action (date(datetime));
>
> GREAT!!! I thought it is possible but I didn't knew how to make such
> indexes.
chael Fuhr" <[EMAIL PROTECTED]>
To: "Andrei Bintintan" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, January 20, 2005 11:33 AM
Subject: Re: [SQL] SQL timestamp to date cast
On Thu, Jan 20, 2005 at 10:52:50AM +0200, Andrei Bintintan wrote:
SELECT DISTINCT(nummer)
FROM user_action
On Thu, Jan 20, 2005 at 10:52:50AM +0200, Andrei Bintintan wrote:
> SELECT DISTINCT(nummer)
> FROM user_action
> WHERE datetime::date='11/11/2004' AND id_action IN (5,6,9)
>
> Now, datetime has the type timestamp. How can I make an index or write
> different this query so that it runs faster?
Yo
Hi,
I have the following query:
SELECT DISTINCT(nummer) FROM user_action WHERE
datetime::date='11/11/2004' AND id_action IN (5,6,9)
Now, datetime has the type timestamp. How can I
make an index or write different this query so that it runs faster? It really
takes some time sometimes.
I am having a bit of difficulty trying to find a solution to this
problem of manipulating timestamps and dates so I hope someone can
enlighten me.
I have a table which contains a column of type timestamp. For each row
I want to find out the dates for the beginning and end of the week
within which
Check this out
http://www.postgresql.org/docs/7.3/static/functions-formatting.html
to_char(departure, 'MM-DD- HH24:MI:SS') as
newformatdeparture
Ted
--- Abdul Wahab Dahalan <[EMAIL PROTECTED]> wrote:
> Hi !
> In my database I've a field "departure" with
> timestamp without time zone
>
On Wed, 22 Oct 2003, Abdul Wahab Dahalan wrote:
> Hi !
> In my database I've a field "departure" with timestamp without time zone
> data type.
> eg :
> departure
> 2003-11-01 14:29:46
Maybe
SET DateStyle TO 'German' ;
SELECT replace(departure,'.','-') from ;
is close to what you want,
but be
Dnia 2003-10-22 10:02, Użytkownik Abdul Wahab Dahalan napisał:
Hi !
In my database I've a field "departure" with timestamp without time zone
data type.
eg :
departure
2003-11-01 14:29:46
Now I want to select it as in this format 01-11-2003 14:29:46 How should
I do it?.
You can find it in docume
Hi !
In my database I've a field "departure" with timestamp without time zone
data type.
eg :
departure
2003-11-01 14:29:46
Now I want to select it as in this format 01-11-2003 14:29:46 How should
I do it?.
Thank you for any help.
---(end of broadcast)--
"Anagha Joshi" <[EMAIL PROTECTED]> writes:
> Here is output from PostgreSQL 7.2.4:
> =20
> trapdb=3D# select cast(datetime(1057637700) as timestamp);
> timestamp
> ---
> 2003-07-07 22:45:00+05:30
> (1 row)
> =20
> I've migrated from PG - 7.1.2 to 7.2.4.
Not sure w
Hi,
Ive observed this:
Here is output from PostgreSQL 7.1.2:
trapdb=# select cast(datetime(1057637700) as timestamp);
?column?
---
2003-07-08 09:45:00+05:30
(1 row)
Here is output from PostgreSQL 7.2.4:
trapdb=# select cast(datetime
I am use PostgreSQL 7.2.1 in Redhat Linux 7.2
Java 1.4.x
When I do JDBC
String sql = "SELECT datetime_column FROM mytable";
Timestamp ts = resultSet.getTimestamp(2);
If the timestamp return format like
2003-07-15 13:20:00.20+00
then is OK. But if timestamp return like
2003-07-15 13:20:00.
Thanks, that made the trick.
On Fri, 4 Apr 2003, Stephan Szabo wrote:
>
> On Fri, 4 Apr 2003, Claude wrote:
>
> > I have a table a field with timestamps in seconds since epoch and I would
> > like to get a human readable date... but it seems that postgresql 7.3 does
> > not support the datetime(
On Fri, 4 Apr 2003, Claude wrote:
> I have a table a field with timestamps in seconds since epoch and I would
> like to get a human readable date... but it seems that postgresql 7.3 does
> not support the datetime(), timestamp(), timestamptz() functions...
I'd say probably something like:
CAST(
On 4 Apr 2003 at 22:18, Claude wrote:
>
> Hi,
>
> I have a table a field with timestamps in seconds since epoch and I
> would like to get a human readable date... but it seems that
> postgresql 7.3 does not support the datetime(), timestamp(),
> timestamptz() functions...
>
> I tried the exampl
Hi,
I have a table a field with timestamps in seconds since epoch and I would
like to get a human readable date... but it seems that postgresql 7.3 does
not support the datetime(), timestamp(), timestamptz() functions...
I tried the example in:
http://archives.postgresql.org/pgsql-bugs/2002-07/m
On Sun, 6 Oct 2002, Brian Ward wrote:
And Brian, since this *is* a mailing list, it would be polite to use a
valid email address. I suggest that you either unsubscribe or fix the
email address.
Mail to [EMAIL PROTECTED] bounces with the message: '550 Invalid
recipient: <[EMAIL PROTECTED]>'.
On Sun, 6 Oct 2002, Brian Ward wrote:
> How do I create a time stamp column in a table that default to the current
> time
> when a row is inserted?
> I tried putting in now() as the default but I have something wrong
> with the syntax or something I think
> Any one have an example of a table crea
On Sun, 2002-10-06 at 09:41, Brian Ward wrote:
> How do I create a time stamp column in a table that default to the current
> time
> when a row is inserted?
> I tried putting in now() as the default but I have something wrong
> with the syntax or something I think
> Any one have an example of a ta
How do I create a time stamp column in a table that default to the current
time
when a row is inserted?
I tried putting in now() as the default but I have something wrong
with the syntax or something I think
Any one have an example of a table creation script that has a timestamp
column?
---
I think I got it but I'm not sure if it's correct.
I put the default as CURRENT_TIMESTAMP but I though I had read that was
being taken out and only now was supported?
Does anyone know?
"Brian Ward" <[EMAIL PROTECTED]> wrote in message
anphrb$24se$[EMAIL PROTECTED]">news:anphrb$24se$[EMAIL PROTEC
Keith,
> We have moved from 7.1 to 7.2 and get the following error when
> extracting dates.
>
> Bad timestamp external representation ' '
>
>
> eg. INSERT INTO mytable VALUES('1', '2001-09-24')
The error, as you have described it, is highly unlikely. You will
need to provide more informatio
We have moved from 7.1 to 7.2 and get the following error when
extracting dates.
Bad timestamp external representation ' '
eg. INSERT INTO mytable VALUES('1', '2001-09-24')
Seems to accept dd/mm/
(What about ISO default?)
--
Keith Gray
Technical Services Manager
Heart Consulting Servi
Hello!
i'm using PostgreSQL 7.2.1 and got strange parse errors..
could somebody tell me what's wrong with this timestamp query example?
PostgreSQL said: ERROR: parser: parse error at or near "date"
Your query:
select timestamp(date '1998-02-24', time '23:07')
example is from PostgreSQL help an
All,
Perhaps I'm not sing hte correct datatype, but I'd like to be able to convert
a timestamp over to an unsigned long to be used within C code and compare
to the output of time().
I can't seem to see any easy way of doing this using the built in
stuff for postgresql.
Ideas?
Perhaps I'
There appears to be a bug in timestamp/interval addition. It happens in
both PG version 7.0.2 and 7.1. There is a duplicate day (2001 Sep 07) and
a missing day (2002 Apr 04). I discovered this by accident when I asked
the interface I'm writing for a 365 day long calendar.. Interestingly,
the
[EMAIL PROTECTED] writes:
> I've tried using variations of to_char and to_timestamp but can't seem
> to get the timestamp as a value of milliseconds since the Epoch (Jan
> 1, 1970).
regression=# select date_part('epoch','2001-02-05 17:23:26.123456-08'::timestamp);
date_part
--
On Tue, 6 Feb 2001 [EMAIL PROTECTED] wrote:
> Hi,
>
> I'm using a timestamp field called date_created. Whenever I select it
> I get:
>
> select date_created from tbl_user;
> date_created
>
> 2001-02-05 17:23:26-08
> 2001-02-05 17:45:39-08
> 2001-02-03 0
Hi,
I'm using a timestamp field called date_created. Whenever I select it
I get:
select date_created from tbl_user;
date_created
2001-02-05 17:23:26-08
2001-02-05 17:45:39-08
2001-02-03 03:58:53-08
(3 rows)
I've tried using variations of to_char and to_t
Hi,
The user manual said the Timestamp type has a microsecond accuracy, but how can
I display the results of a query with microseconds ?
Without formatting the output has an accuracy of 10^-2 second, and with
formatting it is even worse since the formatting strings do not accept anything
under t
-- Forwarded message --
Date: Sat, 23 Sep 2000 15:58:06 +0200 (CEST)
From: Alessandro Rossi <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: timestamp difference in hours?
I found in a post from Jeff MacDonald the following example:
bignose=> \d foo
Table "foo"
"Mitch Vincent" <[EMAIL PROTECTED]> writes:
> Looks like that index scan is very unattractive...
Yes, though not as bad as the cost estimator thinks (almost a 5:1 ratio
in estimated cost, but hardly any difference in real runtime). Still
have some work to do in tweaking the estimates, obviously.
ive... I'll look for some other
ways to speed up the query a bit..
Thanks!
-Mitch
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Mitch Vincent" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, July 21, 2000 1:26 PM
S
"Mitch Vincent" <[EMAIL PROTECTED]> writes:
> select * from applicants as a where (a.created::date > '05-01-2000' or
> a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted >
> a.created then a.resubmitted else a.created end) desc limit 10 offset 0
> There is one of the queries..
select * from applicants as a where (a.created::date > '05-01-2000' or
a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted >
a.created then a.resubmitted else a.created end) desc limit 10 offset 0
There is one of the queries.. I just remembered that the order by was added
since
"Mitch Vincent" <[EMAIL PROTECTED]> writes:
> A while back I as told (by Tom Lane I *think*) that timestamp (previously
> datetime) fields couldn't be indexed as such
That's certainly not true now, if it ever was...
regression=# create table applicants(resubmitted timestamp);
CREATE
regression=#
A while back I as told (by Tom Lane I *think*) that timestamp (previously
datetime) fields couldn't be indexed as such and that I should index them
using this method :
CREATE INDEX "applicants_resubmitted" on "applicants" using btree ( date
("resubmitted") "date_ops" );
Since almost all the que
Bernie Huang wrote:
>
> table
> -
> ...
> borrow timestamp
> return timestamp
>
> Now, I thought timestamp is in sec eg; 35434434, but in PostgreSQL, it
> shows up as datetime datatype eg; 2000-06-07 17:00:05-07.
>
> I was wondering is this format a correct one for timestamp, or is it a
Hi,
I have Postgres 7.0, and I created a table
table
-
...
borrow timestamp
return timestamp
Now, I thought timestamp is in sec eg; 35434434, but in PostgreSQL, it
shows up as datetime datatype eg; 2000-06-07 17:00:05-07.
I was wondering is this format a correct one for timestamp, or i
70 matches
Mail list logo
