[SQL] Part 1 of several - Converting a varchar to an interval
Hello, I have several large tables, over 100 million records each. One of the fields is callee 'duration'. It is a varchar that contains what is essentially an integer that is the duration of an event in milleseconds. Could someone tell me a simple way to convert a value such as 134987 stored in a varchar into an interval? This will dovetail with my next question. Thanks for all the help both now and previous. Shawn -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question 2 Interval and timestamptz
Hello! The second part of my question is: Given a value as an interval, see previous posting, is there a simply method to take a given timestamptz value and a given interval value and create the sum or difference of the 2 in timestamptz format? The scenario is that the afore mentioned tables, see previous post, in addition to the duration varchar field, also have a field called event_at_utc which is a timestamptz type. I need to calculate the end time of the event given the interval calculated from the duration (varchar) field. Any all all help is greatly appreciated. Shawn -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Export Access 97 to PostgreSQL
On Wednesday 26 March 2008 11:46:43 Shavonne Marietta Wijesinghe wrote: > Hello > > I have a db in MS Access 97 and now i have to import the data in > PostgreSQL. I can create the table structure in PostgreSql but in what > format can i export the table from Access so Postgresql can read it? > > Thanks > > Shavonne Hello Shavonne, I'm no expert but I have often used either the tab or comma delimited format. It requires that the fields in the PostgreSQL table are in the same order as the original and that the data types are formatted similarly, but it has worked for me. Also a sed and awk'ing of the results can clean up alot of formatting issues. example, one of my fields is in the timestamp with time zone format but MS-SQL doesn't differentiate Time Zones, at least not in the database I'm pulling from. I use this line to add the UTC to the field " sed 's/\t/ UTC\t/1'". Shawn -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Part 1 of several - Converting a varchar to an interval
On Wednesday 26 March 2008 16:16:57 you wrote:
> select ('134987'::int/1000.00) * interval ' 1 second' ;
> ?column?
> --
> 00:02:14.987
> (1 row)
Thanks Adrian!
Wow!
Shawn
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question 2 Interval and timestamptz
Awesome Adrian!
Thanks! Just what I needed.
On Wednesday 26 March 2008 16:24:34 you wrote:
> -- Original message --
> From: Shawn <[EMAIL PROTECTED]>
>
> > Hello!
> >
> > The second part of my question is:
> >
> > Given a value as an interval, see previous posting, is there a simply
> > method to take a given timestamptz value and a given interval value and
> > create the sum or difference of the 2 in timestamptz format?
> >
> > The scenario is that the afore mentioned tables, see previous post, in
> > addition to the duration varchar field, also have a field called
> > event_at_utc which is a timestamptz type. I need to calculate the end
> > time of the event given the interval calculated from the duration
> > (varchar) field.
> >
> > Any all all help is greatly appreciated.
> >
> > Shawn
>
> select '2008-03-26 09:21:44':: timestamptz + (('134987'::int/1000)*
> interval '1 second'); ?column?
>
> 2008-03-26 09:23:58-07
> (1 row)
>
> --
> Adrian Klaver
> [EMAIL PROTECTED]
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
On Wednesday 26 March 2008 17:14:28 Tom Lane wrote: > Or even easier: > > regression=# select 134987 * interval '1 msec'; > ?column? > -- > 00:02:14.987 > (1 row) > > > regards, tom lane Tom and Adrian, i am trying to incorporate the solution you gave into a function, trying to save some typing. Its keeps throwing a syntax error: edacs=# create or replace function dur_interval_msec(char) returns interval as 'select ($1 * interval '1 msec');' language sql immutable returns null on null input; ERROR: syntax error at or near "1" LINE 2: as 'select ($1 * interval '1 msec');' ^ obviously it doesn't like the extra single quotes around the 1 msec. Any suggestions for a work around? Shawn -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: FW: Re: [SQL] Part 1 of several - Converting a varchar to an interval
On Wednesday 26 March 2008 23:12:07 Rodrigo E. De León Plicet wrote: > Use dollar quoting, e.g.: > > create or replace function dur_interval_msec(int) returns interval > as > $$ > select ($1 * interval '1 msec'); > $$ > language sql > immutable > returns null on null input; Perfect! Great! Thank you! There are so many things about SQL I don't know Shawn -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Dropping Functions
Hi Guys, Just a quick question. Is there a known problem with dropping function in 8.2.6? I can make them but they can't be deleted. Shawn -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Dropping Functions
On Thursday 27 March 2008 00:13:26 Adrian Klaver wrote: > On Wednesday 26 March 2008 5:05 pm, Shawn wrote: > > Hi Guys, > > > > Just a quick question. Is there a known problem with dropping function > > in 8.2.6? I can make them but they can't be deleted. > > > > Shawn > > They will not deleted if something else depends on them i.e. a trigger. > Can you show the error message if any? > -- > Adrian Klaver > [EMAIL PROTECTED] Thanks Adrian, Sorry to bother you, I found it. I had 2 _ characters in the name and I was trying to delete with only 1. Talk about bonehead Shawn -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Cumulative result with increment
Hello, I am struggling with what is probably a simple problem but I am coming up blank. In the interest of full disclosure I am not a very savy programmer by any stretch. I have a table of data from an external source which contains numbers of events per operating group per hour per day, kind of like this: Group | events | duration | timestamp The Group, events, and duration (milliseconds) are all integers in char format. Timestamp is as stated. The records are at 1 hour increments. I need to sum the events and duration fields in 8 hour (shifts, mid to 8AM, 8AM to 4PM, etc). Id like the output to be in table format ala: Group | events | duration | date| shift --+-++---+--- 100 | 26 |00:00:25.00 |2011-01-01 | Day I have a function to convert the duration to an interval already and can make one to do the shift labeling. Its the rest that has me stumped. Any suggestions or direction? As always, your help is greatly appreciated. -- Sincerely, Shawn Tayler -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Cumulative result with increment
Thanks Steve, That did the trick! I appreciate the help Shawn On Mon, 2011-02-07 at 14:13 -0800, Steve Crawford wrote: > On 02/07/2011 01:11 PM, Shawn Tayler wrote: > > Hello, > > > > I am struggling with what is probably a simple problem but I am coming > > up blank. In the interest of full disclosure I am not a very savy > > programmer by any stretch. > > > > I have a table of data from an external source which contains numbers of > > events per operating group per hour per day, kind of like this: > > > > Group | events | duration | timestamp > > > > The Group, events, and duration (milliseconds) are all integers in char > > format. Timestamp is as stated. The records are at 1 hour increments. > > I need to sum the events and duration fields in 8 hour (shifts, mid to > > 8AM, 8AM to 4PM, etc). > > > > Id like the output to be in table format ala: > > > > Group | events | duration | date| shift > > --+-++---+--- > > 100 | 26 |00:00:25.00 |2011-01-01 | Day > > > > I have a function to convert the duration to an interval already and can > > make one to do the shift labeling. Its the rest that has me stumped. > > > > Any suggestions or direction? > > > > As always, your help is greatly appreciated. > > > > I'm not sure exactly what you want but it sounds like you just want an > output column that has the shift instead of timestamp. You can then sum > on that column. Don't know what your shifts are called, but this will > give you dog-, day- and night-shifts based on your times: > > case > when extract(hour from '2011-01-12 1600'::timestamptz)< 8 then 'dog' > when extract(hour from now())< 16 then 'day' > else 'night' > end as shiftname > > This can be used for grouping as well as display. > > Cheers, > Steve > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Creating a function with single quotes
Hello, This has me befuddled. I am trying create a simple experiment, rather new to SQL and I am running into an issue with single quotes. All I can find on creating a function states the procedure should be contained within single quotes. My problem comes when I want to use a textual representation of an interval. create function csd_interval(integer) returns interval as 'BEGIN RETURN $1 * interval '1 msec' END;' LANGUAGE 'plpgsql'; it always fails at the '1 msec' point. Suggestions? -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
Hello Jasen and the List, I tried the $$ quote suggestion: create function f_csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; Here is what I got: edacs=# \i 'f_csd_interval.sql' psql:f_csd_interval.sql:7: ERROR: syntax error at or near "END" LINE 1: SELECT ( $1 * interval '1 msec') END ^ QUERY: SELECT ( $1 * interval '1 msec') END CONTEXT: SQL statement in PL/PgSQL function "f_csd_interval" near line2 edacs=# The error at or near END is curious. There must be something wrong in the line before it but I can't see it. Suggestions? In case it matters, the server is v8.2.11 compiled from source on Slackware 11.0 and the terminal is v8.3.6 running on Ubuntu v8.10. On Fri, 2009-02-20 at 08:11 +, Jasen Betts wrote: > On 2009-02-19, Shawn Tayler wrote: > > Hello, > > > > This has me befuddled. I am trying create a simple experiment, rather > > new to SQL and I am running into an issue with single quotes. All I can > > find on creating a function states the procedure should be contained > > within single quotes. My problem comes when I want to use a textual > > representation of an interval. > > > > create function csd_interval(integer) returns interval as > > 'BEGIN > > RETURN $1 * interval '1 msec' > > END;' > > LANGUAGE 'plpgsql'; > > > > it always fails at the '1 msec' point. > > > > Suggestions? > > you need to quote the inner quotes, > > create function csd_interval(integer) returns interval as > 'BEGIN > RETURN $1 * interval ''1 msec'' > END;' > LANGUAGE 'plpgsql'; > > when the function itself uses single quotes in literals this quickly > becomes confusing, and so "dollar quoting" was invented. > > create function csd_interval(integer) returns interval as > $$BEGIN > RETURN $1 * interval '1 msec' > END;$$ > LANGUAGE 'plpgsql'; > -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a function with single quotes
Hi Leif! Thank you to you and the group. Worked like a charm. The SQL language was the key Shawn On Fri, 2009-02-20 at 15:12 +0100, Leif B. Kristensen wrote: > You should place a semicolon at the end of the RETURN line, and > remove > the one after END, > > BTW, simple functions as this are better written in the SQL language. > I > can't speak for the validity of the code itself, but you can rewrite > it > as > > create function f_csd_interval(integer) returns interval as $$ > SELECT $1 * interval '1 msec' > $$ LANGUAGE SQL; -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: Fwd: Re: [SQL] Creating a function with single quotes
Ah.. Missed that one. Thank you Adrian! Shawn On Fri, 2009-02-20 at 06:27 -0800, Adrian Klaver wrote: > Actually you need both semicolons. One after the RETURN statement and > one after > the END statement > See below for full details: > http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] varchar value comparisons not working?
Hello, I'm doing a quick comparison between a couple tables, trying to cleanup some inconsistencies, and what should be a simple check between 2 tables doesn't seem to be working. psql is 8.3.7 and server is 8.2.13. I run the following: select sfd.lid as sflid,sd.lid as slid,sfd.serial from sfd,shawns_data sd where sfd.serial = sd.serial_number order by sfd.lid; the lid columns in both tables should be identical, but as you see in this sample, they do differ: sflid | slid | serial ---+---+-- 14056 | 14056 | 9614583 14057 | | 9614984 14058 | 14058 | 9614737 14059 | 14059 | 9614579 14060 | | 9614827 14061 | 14061 | 9614726 14062 | 14062 | 9614966 14063 | 14063 | 9615079 So running this query: select count(*) from sfd,shawns_data sd where sfd.serial = sd.serial_number and sfd.lid != sd.lid; I should show some rows that do not match, at least 2 (there are more than shown). But instead I get this: count --- 0 (1 row) What am I doing wrong? -- Sincerely, Shawn Tayler Radio Network Administrator Washoe County Regional Communications System Telecommunications Division Technology Services Department County of Washoe State of Nevada Ofc (775)858-5952 Cell (775)771-4241 FAX (775)858-5960 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
