Thread: How do I convert a timestamp with time zone to local time?
Greetings!
What is the best way to convert a time expressed as a timestamp with time zone into a timestamp in the local time zone without knowing what the local time zone is?
Thank you.
RobR
Re: How do I convert a timestamp with time zone to local time?
From
"Valter Douglas Lisbôa Jr."
Date:
On Tuesday 29 July 2008 15:07:46 Rob Richardson wrote: > Greetings! > > What is the best way to convert a time expressed as a timestamp with > time zone into a timestamp in the local time zone without knowing what > the local time zone is? > > Thank you. > > RobR SELECT extract (epoch from your_time_field) from your_table; SELECT to_timestamp(your_epoch_field) from your_table; -- Valter Douglas Lisbôa Jr. Sócio-Diretor Trenix - IT Solutions "Nossas Idéias, suas Soluções!" www.trenix.com.br contato@trenix.com.br Tel. +55 19 3402.2957 Cel. +55 19 9183.4244
Thank you very much, sir. After posting I realized that my question did not cover my problem. I also need to calculate if a given time is within daylight savings time or not. The actual situation is this: I have a table that contains the time at which an event occurred and an estimate of how long it will be before a second event occurs. That interval is likely to be in the range of 2-3 days. I need to handle the case in which that span includes the moment at which daylight savings time status changes. The table stores the event time in both local time and UTC time. So, I need a way to calculate whether the time at the end of the interval will be DST or not. I also have a table named "sys_info" that contains a single record for system-wide information. I can expand that table if I want. The easiest way I can think of to do what I need is merely to store the DST start and end dates in that table, and then see if the date I'm checking is between them or not. But that won't work either, since the dates change. In the US, DST runs from the second Sunday in March to the first Sunday in November. I can hard-code to that rule (which is what I'm doing for now), but I can't very well store it in a database. And do other countries use DST, and if so, how do they define it? I think that, if I'm careful, I can write my function so I'll be converting everything I need to UTC before I start, and then I won't convert anything back until the very end, and I'll never have to worry about whether I'm spanning the DST change or not. It would still be nice to have a function that will check DST for a given date, but I hope I can avoid needing it. Thanks again! RobR
Greetings again! A few days ago, I visited a customer's site to talk about administering our system, which is developed around a PostGres database. One of the topics was how to back up the database. I described the process of using PgAdmin to back up and restore a database, and I said a backup should be done every night. I was asked how to automate the procedure, and I couldn't answer. A database administrator said, "There's got to be a way. Otherwise, PostGres wouldn't have survived". I agree with him. The only answers I've found on the Internet involve creating a password-less account and using that to run pg_dump. What is the official best way to automatically back up a PostGres database? Thank you very much. RobR
On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote: > I was asked how to automate the procedure, > and I couldn't answer. The options are manifold! http://www.postgresql.org/docs/8.3/interactive/backup.html
On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote: > Greetings again! > > A few days ago, I visited a customer's site to talk about > administering > our system, which is developed around a PostGres database. One of the > topics was how to back up the database. I described the process of > using PgAdmin to back up and restore a database, and I said a backup > should be done every night. I was asked how to automate the > procedure, > and I couldn't answer. A database administrator said, "There's got to > be a way. Otherwise, PostGres wouldn't have survived". I agree with > him. The only answers I've found on the Internet involve creating a > password-less account and using that to run pg_dump. What is the > official best way to automatically back up a PostGres database? There's no one best way. A simple way is to use pg_dump or pg_dumpall, running on the same machine as the database connecting via a unix socket using ident authentication to dump a consistent view of the database out to a file. http://www.postgresql.org/docs/8.3/interactive/backup.html discusses several other ways. Cheers, Steve
On Tue, Jul 29, 2008 at 1:28 PM, Christophe <xof@thebuild.com> wrote: >> I was asked how to automate the procedure, >> and I couldn't answer. > http://www.postgresql.org/docs/8.3/interactive/backup.html Regarding the SQL backup option for small databases, I use an OS task scheduler ( *nix Cron-job, MS task-scheduler) to automatically call my custom script file designed to handle that backing up of my databases. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Slony-I replication is also a viable choice for backups. On Tue, Jul 29, 2008 at 1:34 PM, Richard Broersma <richard.broersma@gmail.com> wrote: > On Tue, Jul 29, 2008 at 1:28 PM, Christophe <xof@thebuild.com> wrote: > >>> I was asked how to automate the procedure, >>> and I couldn't answer. >> http://www.postgresql.org/docs/8.3/interactive/backup.html > > > Regarding the SQL backup option for small databases, I use an OS task > scheduler ( *nix Cron-job, MS task-scheduler) to automatically call my > custom script file designed to handle that backing up of my databases. > > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- - John L Cheng
On Tue, Jul 29, 2008 at 2:24 PM, Rob Richardson <Rob.Richardson@rad-con.com> wrote: > Greetings again! > > A few days ago, I visited a customer's site to talk about administering > our system, which is developed around a PostGres database. One of the > topics was how to back up the database. I described the process of > using PgAdmin to back up and restore a database, and I said a backup > should be done every night. I was asked how to automate the procedure, > and I couldn't answer. A database administrator said, "There's got to > be a way. Otherwise, PostGres wouldn't have survived". I agree with > him. The only answers I've found on the Internet involve creating a > password-less account and using that to run pg_dump. What is the > official best way to automatically back up a PostGres database? For future reference, you'll get less scattered ansewrs if you tell us what OS you're running on, specifically whether or not it's unix or windows. In unix you can write a handy dandy bash shell script like this: #!/bin/bash if (! (pg_dump dbname > /dir/filename.sql)); then echo "Backup failed"|sendmail -s "admin alert" "myname@mydomain"; fi; or something like that to run as a crontab job.
On 2008-07-29 23:47, John Cheng wrote: > Slony-I replication is also a viable choice for backups. No, it's not. Redundancy is not a substitute for backups. Slony will not help you if you do by mistake "delete from important_table" - as a copy will also have all rows deleted. For backups I'd recommend for example pg_dump and duplicity run from cron. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh
On Tue, Jul 29, 2008 at 04:24:08PM -0400, Rob Richardson wrote: > him. The only answers I've found on the Internet involve creating a > password-less account and using that to run pg_dump. What is the > official best way to automatically back up a PostGres database? using pg_dump - yes. making it's account passwordless - not necessarily. you can use various methods of getting access to database without password. you can use "ident sameuser" authentication method in pg_hba.conf. you can use pgpass file, use can use PGPASSWORD environment variable. there are also another ways, but these look the best for me. depesz -- Linked in: http://www.linkedin.com/in/depesz jid/gtalk: depesz@depesz.com aim: depeszhdl skype: depesz_hdl
Tomasz Ostrowski wrote: > On 2008-07-29 23:47, John Cheng wrote: >> Slony-I replication is also a viable choice for backups. > > No, it's not. Redundancy is not a substitute for backups. Slony will not > help you if you do by mistake "delete from important_table" - as a copy > will also have all rows deleted. > > For backups I'd recommend for example pg_dump and duplicity run from cron. Yep ... there's nothing like having a plain-text, or at least stable format (-Fc) copy of your data around for disaster recovery. It's not particularly time and space efficient, but it's certainly reassuring. For anything important, especially where you cannot afford to lose a whole day's work, I'd also want to consider using log shipping based PITR. That way, you lose at most (depending on configuration) say 15 minutes. Real-time replication to a remote site with slony or similar can help protect you against fire, theft, etc though. -- Craig Ringer
I created this script sometime ago and it works fine for me and others.. Maybe it might work for you http://www.zeroaccess.org/postgresql-backup 1.0RC1 is pretty stable I have ran it for 3 weeks without any problems Quoting "Rob Richardson" <Rob.Richardson@rad-con.com>: > Greetings again! > > A few days ago, I visited a customer's site to talk about administering > our system, which is developed around a PostGres database. One of the > topics was how to back up the database. I described the process of > using PgAdmin to back up and restore a database, and I said a backup > should be done every night. I was asked how to automate the procedure, > and I couldn't answer. A database administrator said, "There's got to > be a way. Otherwise, PostGres wouldn't have survived". I agree with > him. The only answers I've found on the Internet involve creating a > password-less account and using that to run pg_dump. What is the > official best way to automatically back up a PostGres database? > > Thank you very much. > > RobR > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.