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?
