5

When I try to convert the timestamp in the following query, using bash 

docker exec compose_TSOwncloudMySQL_1 mysql -h localhost -udockerdev -pdocker owc -e "
SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y%m%d timestamp%h:%i:%s') AS 'date_formatted',
    oc_ldap_user_mapping.ldap_dn,
    oc_activity.subject,
    oc_activity.file,
    oc_activity.subjectparams
FROM oc_activity INNER JOIN oc_ldap_user_mapping ON oc_activity.user = oc_ldap_user_mapping.owncloud_name 
ORDER BY oc_activity.timestamp;"> /home/dockerdmz/tsowncloud/log_owc/owc_$DATE.log`

I have this error:

ERROR 1582 (42000) at line 2: Incorrect parameter count in the call to native function 'FROM_UNIXTIME'

When I run this query in MySQL admin page, it works well. 

SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y%m%d %h:%i:%s') AS 'date_formatted',
oc_ldap_user_mapping.ldap_dn,
oc_activity.subject, 
oc_activity.file, 
oc_activity.subjectparams
FROM oc_activity INNER JOIN oc_ldap_user_mapping ON oc_activity.user = oc_ldap_user_mapping.owncloud_name
ORDER BY oc_activity.timestamp;

When I run this bash code (without timestamp conversion), it works well

Docker exec compose_TSOwncloudMySQL_1 mysql -h localhost -udockerdev -pdocker owc -e "
SELECT oc_activity.timestamp,
    oc_ldap_user_mapping.ldap_dn,
    oc_activity.subject,
    oc_activity.file,
    oc_activity.subjectparams
FROM oc_activity INNER JOIN oc_ldap_user_mapping ON oc_activity.user = oc_ldap_user_mapping.owncloud_name
ORDER BY oc_activity.timestamp;"> /home/dockerdmz/tsowncloud/log_owc/owc_$DATE.log
Breton It
  • 53
  • 1
  • 6
  • What is the word `timestamp` doing in this syntax `'%Y%m%d timestamp%h:%i:%s'` – RiggsFolly Sep 17 '16 at 13:07
  • Why have you got 2 `SELECT`'s in the query `SELECT SELECT` – RiggsFolly Sep 17 '16 at 13:08
  • [What does \` (backquote/backtick) mean in commands?](http://unix.stackexchange.com/questions/27428/what-does-backquote-backtick-mean-in-commands). – Solarflare Sep 17 '16 at 13:19
  • there is just one SELECT in my code. not SELECT SELECT – Breton It Sep 17 '16 at 13:28
  • I edit my post. Timestamp is one element in the oc_activity table – Breton It Sep 17 '16 at 13:35
  • Did you read my link? If you have `\`timestamp\`` in your command line, it will execute the command `timestamp` and replace it with the result message of that command before it passes it to mysql. That's why it results in someting you dont expect. – Solarflare Sep 17 '16 at 14:07
  • thank you. So, how can i get the timestamp value saved in the oc_activity table (oc_activity.timestamp). Then, convert this value as a date ? – Breton It Sep 17 '16 at 14:23

1 Answers1

0

Ugh. It's a bash backtick thing.

Try escaping the backticks with backslashes when you use bash, like so.

 -e " SELECT DATE_FORMAT(FROM_UNIXTIME(\`timestamp\`),   ...

Pro tip: Avoid using reserved words (such as timestamp in your case) for column or table names. That way you don't have to wrap them in backticks in your queries, and you can use the same queries in various contexts (bash, php, etc).

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks for the advice. I'll test your recommendation Monday and I'll let you know. have a nice week end – Breton It Sep 17 '16 at 14:31