apiVersion: batch/v1beta1
kind: CronJob
metadata:
  labels:
    app: nextcloud
    component: count-active-users
  name: ${FQDN}-count
  namespace: ${NS}
spec:
  concurrencyPolicy: Forbid
  failedJobsHistoryLimit: 1
  schedule: '15 15 * * *'
  jobTemplate:
    spec:
      template:
        metadata:
          labels:
            app: nextcloud
            component: count-active-users
        spec:
          restartPolicy: Never
          containers:
            - name: psql
              image: postgres:12-alpine
              command:
                - sh
                - -c
                - >
                    echo {\"30days\":`psql -t -c "select count(*) from preferences where configkey = 'lastLogin' and to_timestamp("configvalue"::integer) > ('now'::timestamp - '1 month'::interval);" | xargs`,\"90days\":`psql -t -c "select count(*) from preferences where configkey = 'lastLogin' and to_timestamp("configvalue"::integer) > ('now'::timestamp - '3 month'::interval);" | xargs`}
              env:
              - name: PGHOST
                value: pg-${NS}
              - name: PGDATABASE
                value: nextcloud
              - name: PGUSER
                value: postgres
              - name: PGPASSWORD
                valueFrom:
                  secretKeyRef:
                    key: password
                    name: postgres.pg-${PG_DOMAIN}.credentials
#psql -h pg-desami-es -U postgres nextcloud -t -c "select count(*) from preferences where configkey = 'lastLogin' and to_timestamp("configvalue"::integer) > ('now'::timestamp - '1 month'::interval);"
#psql -t -c "select count(*) from preferences where configkey = 'lastLogin' and to_timestamp("configvalue"::integer) > ('now'::timestamp - '1 month'::interval);"