Re: [SQL] converting timestamps to ints
Morgan, > I am writing an app that will need to do some calcs based on > differences > between when a record is first inserted and when it is last updated. > I have seen a lot of column::data-type in this list but can't find > the > relevant section in the documentation. Can anyone point me in the > right > direction? Hmmm... apparently the docs on date/time data types could use some work. Or some highlighting. Or something. We get this question every week. I'll do it -- can I submit a FAQ in plain text? Anyway, Morgan, thanks to Postgres' robust and ANSI-compliant support of operators, computing intervals is quite simple. DATETIME is a "pure" time value; DATE is an integer representation of time, with days as whole numbers. Thus: DATETIME - DATETIME = INTERVAL DATE - DATE = INTEGER I don't know what's easier for you to work with; DATE/INTEGER is easier for math, and DATETIME/INTERVAL is easier for calendar comparisons. See the docs on data types, functions, and operators at PostgreSQL.org. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] where'd the spaces come from
Hi Bruce, a fix for what? If you're meaning the leading space, then the fix is in the followup post that I made to my original quiestion. i.e. psql -c "select to_char(12,'xFM000');" to_char - x012 (1 row) The 'FM' removes the space. Gary On Thursday 02 August 2001 4:50 pm, Bruce Momjian wrote: > Does anyone have a fix for this? > > > From: "Gary Stainburn" <[EMAIL PROTECTED]> > > > > > psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' || > > > to_char(m.mnumber,'00') as unitno from teams t, members m > > > where m.mteam = t.tid;" > > >unitno > > > - > > > SW/ 041- 03 > > > SW/ 041- 05 > > > > Looks like a buglet in to_char()s handling of numbers (unless I > > misunderstand the way the formatting is supposed to work). > > > > select '[' || to_char(12,'x000') || ']'; > > ?column? > > -- > > [x 012] > > > > If you're running the current version, might be worth posting a bug > > report. You can work around it with something like: > > > > ... substr(to_char(t.tnumber,'000'),2,3) ... > > > > HTH > > > > - Richard Huxton > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] where'd the spaces come from
From: "Bruce Momjian" <[EMAIL PROTECTED]> > > Hi Bruce, > > > > a fix for what? > > If you're meaning the leading space, then the fix is in the followup post > > that I made to my original quiestion. i.e. > > > > psql -c "select to_char(12,'xFM000');" > > to_char > > - > > x012 > > (1 row) > > > > The 'FM' removes the space. > > So the FM is the correct way to do this, right? There is no bug? Well - it's certainly *unexpected* behaviour isn't it? It is documented though (Karel Zak's given plenty of examples too): "FM suppresses leading zeroes or trailing blanks that would otherwise be added to make the output of a pattern be fixed-width" Some of the examples show the difference too: to_char(12,'9990999.9') => ' 0012.0' to_char(12,'FM9990999.9') => '0012' I think the issue is you look at to_char() and make assumptions if you're not familiar with it. I *seem* to remember someone saying Oracle worked this way. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] where'd the spaces come from
> Hi Bruce, > > a fix for what? > If you're meaning the leading space, then the fix is in the followup post > that I made to my original quiestion. i.e. > > psql -c "select to_char(12,'xFM000');" > to_char > - > x012 > (1 row) > > The 'FM' removes the space. So the FM is the correct way to do this, right? There is no bug? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Feature Requests for 7.2 or 7.3 ...
Chris Ruprecht wrote: > > Hi guys, > > There are a number of features, I would love to see implemented in a future > release of PostGreSQL, more specific, into PL/PGSQL. Is there a central > place somewhere, where we (the PostGreSQL user community, I guess that is), > is able to see what is planned for the future and where we can send some of > the ideas we (constantly) have? > It's nice to have such an active community with developers who actually > listen to what their users have to say (I have some bad experiences with > other systems...:). > > I just looked at the gram.y file for PL/PGSQL, but I have no idea what is > actually going on there. I have a colleague here, who did compiler building > in the past - he might understand the stuff though - maybe I can get him > interested in implementing the one or other thing. > > Keep up the excellence ... > > Chris > there is in fact a todo list trudge trudge dig http://postgresql.wavefire.com/docs/todo.html > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Re: [NOVICE] Knowing new item in table...
--- macky <[EMAIL PROTECTED]> wrote:
> im still in the dark when it comes to postgres.
> is there a way i can
> know if there was a new entry on a table...
That's a pretty common problem.
> scenario...
>
> i have a script that runs every 5 minutes... that
> script checks if there's
> a new entry in that table... and if YES there's a
> new entry ill do some
> processing...
Probably the most straightforward thing to do is to
simply insert a timestamp in each row as it is added
to the database. That way you can do a simple select
to figure out which rows have been added:
SELECT * FROM my_table WHERE insert_time > now() - '5
minutes'::interval
or alternatively
SELECT * FROM my_table WHERE insert_time > '2001-08-03
11:30'
The best part is that creating a column that includes
a timestamp automagically is fairly easy to do.
Simply define your table like this:
CREATE TABLE foo (
insert_time timestamp DEFAULT CURRENT_TIMESTAMP,
nametext
);
Once your table is created you simply insert into
table foo ignoring the insert_time column like so:
INSERT INTO foo (name) VALUES ('Jason');
and your timestamp automagically gets inserted:
processdata=> SELECT * FROM foo;
insert_time| name
+---
2001-08-03 11:32:48-06 | Jason
(1 row)
Pretty neat, huh?
> is there an internal utility that i can use in
> postgres that can tell me
> that this rows a new commers... hehehe
Nope, you have to come up with the logic yourself.
However, PostgreSQL has all kinds of tools that are
really helpful.
> if someone has other ideas on how to deal with this
> speak out...
>
> thanks in advance..
>
> btw..
>my idea is that that table will have an addtional
> column as reference
> lets say column "READ" 1 for yes 0 for NO
>
That would work too, but it would be a lot harder.
For example, you would have to first select all the
rows where READ is 0, do your processing, and then
update all of those rows to 1. You would almost
certainly want to do all of this in a transaction so
that you could roll READ back to 0 if something went
wrong, and you would probably want to lock the table
to boot as you would have to worry about your
processing step taking more than 5 minutes. If it
did, the second transaction would see the last 10
minutes of of inserts as being unread even though the
first transaction was still working on them.
I hope this is helpful,
Jason
__
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
