Date conversion in AWS IoT Rule SQL

Question

I create AWS IoT Rules using Ansible playbook for cloud formation. Below is snippet where I want to create a simple rule to write everything from updated device shadow to S3. Name of Key should have YYYY/MM/DD.

Code snippet for IoT Rule SQL:

my_iot_rule:

    region: "{{ region }}"
    state: present
    name: "{{ envType }}_deviceshadow_rule_v1"
    description: Forward device shadow status to S3
    sql: SELECT * FROM "${aws}/things/#/shadow/get/accepted" WHERE schemaVersion='mySchemaVersion1.0'
    sqlVersion: "2016-03-23"
    actions:
      - s3:
          roleArn: arn:aws:iam::{{ awsAccount.accountId }}:role/iot-rule-engine
          bucketName: "{{ s3.someBucketName}}"
          "key": "deviceshadow/${device.deviceId}**/YYYY/MM/DD/somename.someextention**"

Assume I have a field with epoch time as "time": 1471618669000 which will be in output of SELECT *,time.
How do I convert "time": 1471618669000 to get YYYY, MM and DD in "key"'s value in above. Is there any function in IoT SQL ?
Or How can I get Sysdate in IoT Rule SQL ? Thanks in advance.

score 2 · Answer 1 · edited Jul 01 '21 at 08:13

2

Please use:

key:${parse_time("yyyy", timestamp())}/${parse_time("MM", timestamp())}/${parse_time("dd", timestamp())}/${parse_time("HH", timestamp())}//${timestamp()}

It will create the folder structure like "year/month/date/hour/file_name"

edited Jul 01 '21 at 08:13

tomerpacific

4,704
13
34
52

answered Jul 01 '21 at 06:43

Kishor Werulkar

21
2

should works, please see `parse_time` examples - https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html – Cherry Feb 22 '22 at 16:28

score 0 · Answer 2 · answered May 04 '17 at 04:45

Til today, AWS IOT SQL function has no date function. The close solution is to convert epoch to number of days since 1970-1-1, by deviceshadow/${device.deviceId}/${timestamp()/86400000}/${timestamp()}.

This will group each event message (unique id as timestamp) by days.

This is still not human readable, and I guess the only way is to use lambda to do the date conversion.

Date conversion in AWS IoT Rule SQL

2 Answers2