timesString in PSQL

Posted on
Thu Jan 15, 2015 2:39 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

timesString in PSQL

how do I do in the command line:
/usr/bin/sqlite3 -separator indigo_history.sqlite "SELECT id, strftime('%Y%m%d%H%M%S',ts,'localtime') ,....;"
in PLSQL?

/Library/PostgreSQL/bin/psql indigo_history postgres "SELECT id, strftime('%Y%m%d%H%M%S',ts,'localtime') ,....;" does not work
/Library/PostgreSQL/bin/psql indigo_history postgres "SELECT id, ts ,....;"works but the date string is formatted in a different way..

==> how can I format the returned date/time-string in the query


Karl

[EDIT]
tried:
/Library/PostgreSQL/bin/psql indigo_history postgres -t -A -F' ' -c " SELECT id,CAST(ts as timestamp WITH TIME ZONE), onOffState from device_ .... works but still with -:...

/Library/PostgreSQL/bin/psql indigo_history postgres -t -A -F' ' -c " SELECT id,to_char('Y%m%d%H%M%S',CAST(ts as timestamp)), onOffState from device_ ...

==> erro:
LINE 1: SELECT id,to_char('Y%m%d%H%M%S',CAST(ts as timestamp)), onO...

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

or:
/Library/PostgreSQL/bin/psql indigo_history postgres -t -A -F' ' -c " SELECT id, to_char(ts,'Y%m%d%H%M%S'), onOffState from device_history_292008671"
==>
1 5%m%2%H%M%S t
2 5%m%2%H%M%S t

Posted on
Thu Jan 15, 2015 3:54 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: timesString in PSQL

Code: Select all
/Library/PostgreSQL/bin/psql indigo_history postgres -t -A -F' ' -c " SELECT id, to_char(ts,'YYYYmmddHHMMSS'), onOffState from device_history_292008671"
1 20150112050156 t
2 20150112050118 t


seems to work,

now how do i change to local time zone..

Posted on
Thu Jan 15, 2015 5:41 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: timesString in PSQL

PLSQL:
/Library/PostgreSQL/bin/psql indigo_history postgres -t -A -F' ' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), ...

is equivalent to

SQLite
/usr/bin/sqlite3 -separator ' ' indigo_history.sqlite "SELECT id, strftime('%Y%m%d%H%M%S',ts,'localtime') ....

Posted on
Sat Jan 17, 2015 10:05 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: timesString in PSQL

I am trying to find the Postgres data base file. I can only find the SQLite file. Any idea where it is stored?


Sent from my iPhone using Tapatalk

Posted on
Sat Jan 17, 2015 10:40 am
autolog offline
Posts: 3988
Joined: Sep 10, 2013
Location: West Sussex, UK [GMT aka UTC]

Re: timesString in PSQL

On my system it is at /Library/PostgreSQL/data :)

Posted on
Sat Jan 17, 2015 10:50 am
autolog offline
Posts: 3988
Joined: Sep 10, 2013
Location: West Sussex, UK [GMT aka UTC]

Re: timesString in PSQL

As a follow up, you might like to look at pgAdmin - it provides a number of useful tools and a Systems Preferences > PostgreSQL Server control option. :)

Posted on
Sat Jan 17, 2015 11:46 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: timesString in PSQL

ok got it.

I am actually trying to convert my sqlite database with ~ 1year of data to PQSL.

Question to Matt/Jay: it looks as if indigo copies the data from sqllite to psql when switch the data base. at least it seems to work for my small test database on my laptop.
After deleting the sqlite data base and restarting everything then switching from pqsl to sqlite, all data gets converted. And it seems that it also works the other way round.

is this correct?
I would like to make sure to not lose my history when I convert sqlite to PSQL.

thanks

Karl

Posted on
Mon Jan 19, 2015 8:43 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: timesString in PSQL

Hi Karl,

kw123 wrote:
Question to Matt/Jay: it looks as if indigo copies the data from sqllite to psql when switch the data base. at least it seems to work for my small test database on my laptop.
After deleting the sqlite data base and restarting everything then switching from pqsl to sqlite, all data gets converted. And it seems that it also works the other way round.


No, I don't believe there is any code that tries to copy over the existing table data. Indigo does, on launch or database change, save out the current device states to the database. So perhaps that is what you are seeing? It'll update it so the table reflects the current snapshot of the system, but all the historical information will be lost.

Image

Posted on
Mon Jan 19, 2015 10:55 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: timesString in PSQL

ok thanks,

then how do I transfer data.

I tried sqlite --> dump file --> import into PSQL, only error messages

has anyone done it?

thanks

Karl

Posted on
Mon Jan 19, 2015 11:27 pm
RogueProeliator offline
User avatar
Posts: 2501
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: timesString in PSQL

I tried sqlite --> dump file --> import into PSQL, only error messages

That's probably the best way, but you'll need to map some data types over in the SQL that is produced. For the auto-incrementing identity fields you will need to change from integer to serial, you may need to change booleans in SQLite (google this, I can't remember exactly), and datetime needs to go to timestamp. There may be others too, but I think your varchars, integers, floats and such SHOULD go across ok.

Adam

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 12 guests

cron