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