[GENERAL] Selecting timestamp from Database
Hello all Pretty sure this should be simple - how can I select a timestamp from a database? The timestamp is stored in the db like this: 2013/04/08 13:54:41 GMT+1 How can I select based on that timestamp? At the simplest level select timestamp from attendance where timestamp = '2013/04/08 13:54:41 GMT+1' ..doesn't obviously work but I've tried all sorts of to_char and to_timestamp combos to no avail.. Any ideas? Cheers Rich
Re: [GENERAL] Selecting timestamp from Database
This doesn't seem to work - take a normal GMT date for example: 2012/12/14 12:02:45 GMT select timestamp from attendance where timestamp = '2012/12/14 12:02:45' ..returns nothing On 8 Apr 2013, at 14:17, Adrian Klaver adrian.kla...@gmail.com wrote: On 04/08/2013 06:03 AM, Richard Harley wrote: Hello all Pretty sure this should be simple - how can I select a timestamp from a database? The timestamp is stored in the db like this: 2013/04/08 13:54:41 GMT+1 How can I select based on that timestamp? At the simplest level select timestamp from attendance where timestamp = '2013/04/08 13:54:41 GMT+1' ..doesn't obviously work but I've tried all sorts of to_char and to_timestamp combos to no avail.. Any ideas? select timestamp from attendance where timestamp = '2013/04/08 13:54:41+1' Cheers Rich -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Selecting timestamp from Database
Sure Timestamp 2013/04/08 12:42:40 GMT+1 2013/04/08 12:42:33 GMT+1 2013/04/07 20:25:11 GMT+1 2013/04/07 20:19:52 GMT+1 2013/04/07 20:19:52 GMT+1 Some are GMT, some are GMT+1 depending on when they were entered. On 8 Apr 2013, at 14:25, Adrian Klaver adrian.kla...@gmail.com wrote: On 04/08/2013 06:22 AM, Richard Harley wrote: This doesn't seem to work - take a normal GMT date for example: 2012/12/14 12:02:45 GMT select timestamp from attendance where timestamp = '2012/12/14 12:02:45' ..returns nothing Can you show the results of an unconstrained SELECT?: select timestamp from attendance limit 5; -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Selecting timestamp from Database
I am running the query straight through PSQL so there are no other programs or adapters. The field definition is just 'timestamp'. I did try that as well - no luck :) Rich On 8 Apr 2013, at 14:36, Adrian Klaver adrian.kla...@gmail.com wrote: On 04/08/2013 06:27 AM, Richard Harley wrote: Sure Timestamp 2013/04/08 12:42:40 GMT+1 2013/04/08 12:42:33 GMT+1 2013/04/07 20:25:11 GMT+1 2013/04/07 20:19:52 GMT+1 2013/04/07 20:19:52 GMT+1 What program are you using to get the above result? What is the field definition for the timestamp column? From your previous post try: select timestamp from attendance where timestamp = '2012/12/14 12:02:45+0' Some are GMT, some are GMT+1 depending on when they were entered. On 8 Apr 2013, at 14:25, Adrian Klaver adrian.kla...@gmail.com mailto:adrian.kla...@gmail.com wrote: On 04/08/2013 06:22 AM, Richard Harley wrote: This doesn't seem to work - take a normal GMT date for example: 2012/12/14 12:02:45 GMT select timestamp from attendance where timestamp = '2012/12/14 12:02:45' ..returns nothing Can you show the results of an unconstrained SELECT?: select timestamp from attendance limit 5; -- Adrian Klaver adrian.kla...@gmail.com mailto:adrian.kla...@gmail.com -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Selecting timestamp from Database
It's
Column|Type |
Modifiers
--+-+---
attendanceid | integer | not null default
nextval('attendance_attendanceid_seq'::regclass)
entered | date| not null default
('now'::text)::date
timeperiod | character(2)|
timestamp| timestamp without time zone | default now()
On 8 Apr 2013, at 14:48, Adrian Klaver adrian.kla...@gmail.com wrote:
On 04/08/2013 06:45 AM, Richard Harley wrote:
I am running the query straight through PSQL so there are no other programs
or adapters.
The field definition is just 'timestamp'.
From psql what do you get if you do?:
\d attendance
I did try that as well - no luck :)
Rich
--
Adrian Klaver
adrian.kla...@gmail.com
Re: [GENERAL] Selecting timestamp from Database
That returns nothings also. But I have spied the problem now:
select ATTENDANCE.timestamp::text from attendance order by timestamp desc
limit 1
return the actual timestamp: 2013-04-08 12:42:40.089952
So the theory I'm wondering about is that the stored data in fact
contains (some values with) fractional seconds, but Richard's
client-side software isn't bothering to show those, misleading him
into entering values that don't actually match the stored data.
Looking at the table directly with psql would prove it one way
or the other.
This is it. It was the psycopg adapter. My bad!!
Thanks Adrian / Tom.
Rich
On 8 Apr 2013, at 14:58, Adrian Klaver adrian.kla...@gmail.com wrote:
On 04/08/2013 06:49 AM, Richard Harley wrote:
It's
Column|Type |
Modifiers
--+-+---
attendanceid | integer | not null default
nextval('attendance_attendanceid_seq'::regclass)
entered | date| not null default
('now'::text)::date
timeperiod | character(2)|
timestamp| timestamp without time zone | default now()
Well timestamp is not time zone aware, so I have no idea where your time zone
offsets are coming from.
What happens if you do:
select timestamp from attendance where timestamp = ' '2012-12-14 12:02:45';
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] relpages sudden increase
On 09/05/12 00:00, Tomas Vondra wrote: On 8.5.2012 19:27, Richard Harley wrote: I currently do nightly database dumps on a ton of small dbs that are increasing around 2-3mb per day. Suddenly, in a recent backup file, one db in particular jumped from 55mb to 122mb overnight. Well, I wouldn't call that 'massive size' but in my experience such sudden changes in sizes are caused by one of these two things (a) modification patterns + slow vacuuming (b) batch updates (e.g. update of a column in the whole table) Given that this is a one-time issue, I'd guess it's (b). Were you doing any such updates or something like that? No I wasn't doing any maintenance work compared to the days and weeks previously when size was increasing by a few mb a day. I did some investigation - One table increased from 8mb to 31mb during a 24hr period. The table is just text so this is highly unusual given that the number of rows DIDN'T increase any more than normal. What do you mean by 'number of rows'? Is that number of live rows, i.e. the number you get from SELECT COUNT(*) FROM ... or the number you get from pg_class as reltuples? I mean the number of live rows. So the size on disk went up unexpectedly but the rows increase was normal and the data in the rows was normal - just like previous days. pg_toast increased from 8mb to 27mb during the same period. The relpages for the table in question increased from 164 to 1088 during the 24hr period. On the live db, the relpages is back to 164 but the size of the table remains massive. Hmmm, I wonder how the number of pages could drop, because that does not happen unless you run VACUUM FULL / CLUSTER or such commands. And that does not happen regularly. Also, how could the table size remain massive when the number of pages dropped to 164? Did you mean a different table or the whole database? The same table. I imported the db dump that suddenly got bigger into a test db and the table in question has 1088 relpages. In the live db, same table, we're back down to 132 and no vacuuming has taken place .. Thanks for your help Rich **
[GENERAL] relpages sudden increase
I currently do nightly database dumps on a ton of small dbs that are increasing around 2-3mb per day. Suddenly, in a recent backup file, one db in particular jumped from 55mb to 122mb overnight. I did some investigation - One table increased from 8mb to 31mb during a 24hr period. The table is just text so this is highly unusual given that the number of rows DIDN'T increase any more than normal. pg_toast increased from 8mb to 27mb during the same period. The relpages for the table in question increased from 164 to 1088 during the 24hr period. On the live db, the relpages is back to 164 but the size of the table remains massive. Using Centos5/postgres 8.1.21 at the moment. Planning to upgrade over summer but not had issues like this before on 8.1. What gives?! Thanks Rich
[GENERAL] Backups
Hello all Very simple question - does pg_dump/dumpall hit the server in terms of database performance? We currently do nightly backups and I want to move to hourly backups but not at the expense of hogging all the resources for 5 mins. Pg_dumpall is currently producing a 1GB file - that's the combined size of around 30 databases and it takes around 5 mins to run. Thanks Rich
Re: [GENERAL] Backups
Thanks for a quick reply. The server has 6 cores, 6GB ram and top gets to 2.3-2.5 load average when running the dumpall. So I assume we are nowhere near this causing performance issues for users? Thanks Rich On 15/03/12 12:21, Bèrto ëd Sèra wrote: Hi Richard, it's no easy answer. If your server has plenty of free resources there won't be trouble, but I do have customers who cannot even imagine of launching a dump in normal traffic hours. How loaded is your box, currently? Cheerio Bèrto On 15 March 2012 12:15, Richard Harley rich...@scholarpack.com mailto:rich...@scholarpack.com wrote: Hello all Very simple question - does pg_dump/dumpall hit the server in terms of database performance? We currently do nightly backups and I want to move to hourly backups but not at the expense of hogging all the resources for 5 mins. Pg_dumpall is currently producing a 1GB file - that's the combined size of around 30 databases and it takes around 5 mins to run. Thanks Rich -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [GENERAL] Is your data okay?
That really helped me, thanks - although I wish someone had told me about that/ before/ I tried to run a nuclear reactor using MSSQL On 27/08/10 07:30, Mike Christensen wrote: I found this tool pretty helpful for validating my architectural decisions.. http://www.howfuckedismydatabase.com/
Re: [GENERAL] Too much logging
Won't log state = all catch everything? Richard On 27/08/10 10:39, Mike Christensen wrote: Hi all - I've noticed my log files for Postgres are getting way too big, since every single SQL statement being run ends up in the log. However, nothing I change in postgresql.conf seems to make a bit of difference.. I've tried restarting postgres, deleting all the existing logs, etc. No matter what I do, every statement is logged. What I want is to only log SQL statements that result in errors. Here's my config options: log_destination = 'stderr' logging_collector = on client_min_messages = error log_min_messages = error log_error_verbosity = default log_min_error_statement = error log_min_duration_statement = 3000 log_statement = 'all' Pretty much everything else log related is commented out.. What am I doing wrong? Thanks! Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
