0

I have at hand the following table (for data migration):

recvtimets     |         recvtime         | fiwareservicepath | entityid |   entitytype    |     attrname     |    attrtype     |               attrvalue               | attrmd
---------------+--------------------------+-------------------+----------+-----------------+------------------+-----------------+---------------------------------------+--------
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.613502,41.143994]} | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 59.1                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:15:57+01:00             | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | temperature      | Number          | 24.9                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | name             | Text            | Rua das Flores                        | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | name             | Text            | Praca do Marques - pole 1             | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | temperature      | Number          | 21.9                                  | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:24+01:00             | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.603802,41.161671]} | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 80.8                                  | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | name             | Text            | Fundacao de Serralves                 | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.65915,41.1591]}    | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 1                                     | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:22+01:00             | []

The first two columns depict the timestamp the application writes to the database (reading from some db). The time for which data was actually recorded however, is the dateObserved attribute in the attrname column and its corresponding value is that shown in attrvalue column.

This means the first 6 rows (having 2019-06-03T14:47:47.88Z in the recvtime column were recorded on 2015-09-01T10:15:57+01:00 (shown on attrvalue column), i.e. dateObserved = 2015-09-01T10:15:57+01:00

The next 6 rows having 2019-06-03T14:47:47.782Z in the recvtime column were recorded on 2015-09-01T10:16:24+01:00, i.e. dateObserved =2015-09-01T10:16:24+01:00

Likewise, the last 6 rows having recvtime as 2019-06-03T14:47:47.696Z were recorded on 2015-09-01T10:16:22+01:00 i.e. dateObserved = 2015-09-01T10:16:22+01:00

The goal is to alter the contents of recvtime column such that these values are replaced by the date recorded as contain in the attrvalue for the respective rows.

Accordingly, the end result becomes as in following table(i.e. the final table to produce this):

recvtimets     |           recvtime         | fiwareservicepath | entityid |   entitytype    |     attrname     |    attrtype     |               attrvalue               | attrmd
---------------+----------------------------+-------------------+----------+-----------------+------------------+-----------------+---------------------------------------+--------
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.613502,41.143994]} | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 59.1                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:15:57+01:00             | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | temperature      | Number          | 24.9                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Rua das Flores                        | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Praca do Marques - pole 1             | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | temperature      | Number          | 21.9                                  | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:24+01:00             | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.603802,41.161671]} | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 80.8                                  | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Fundacao de Serralves                 | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.65915,41.1591]}    | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 1                                     | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:22+01:00             | []

How do I achieve this in SQL?

2
SELECT t1.recvtimets, 
       t2.attrvalue recvtime,
       t1.fiwareservicepath,
       t1.entityid,
       t1.entitytype,
       t1.attrname,
       t1.attrtype,
       t1.attrvalue,
       t1.attrmd
FROM tablename t1
JOIN tablename t2 ON t1.recvtimets = t2.recvtimets
                 AND t2.attrname = 'dateObserved'
WHERE t1.attrname != 'dateObserved'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.