Want to update dates in date column, with the past dates from yesterday's date

Question

This sounds very strange use case, but I want to update date column with some data. Please find below table. And I'm working on data which is in the JSON format.

CityName | data1 | data2 | date

Mumbai   | 1.234 | 2.3456| Sat Jan 20 2018 12:00:00 GMT+0000 (UTC)
Mumbai   | 6.234 | 2.3456| Sat Jan 20 2018 18:00:00 GMT+0000 (UTC) 
Mumbai   | 1.234 | 2.3456| Sun Jan 21 2018 12:00:00 GMT+0000 (UTC) 
Mumbai   | 2.234 | 2.3456| Sun Jan 21 2018 18:00:00 GMT+0000 (UTC)
Mumbai   | 1.234 | 2.3456| Sun Jan 21 2018 24:00:00 GMT+0000 (UTC)
Mumbai   | 1.234 | 2.3456| Sun Jan 21 2018 00:00:00 GMT+0000 (UTC)
Mumbai   | 8.234 | 2.3456| Fri Jan 19 2018 01:00:00 GMT+0000 (UTC)
Mumbai   | 3.334 | 2.3456| Fri Jan 19 2018 12:00:00 GMT+0000 (UTC)
Mumbai   | 8.214 | 2.3456| Fri Jan 19 2018 14:00:00 GMT+0000 (UTC)
Mumbai   | 19.234| 2.3456| Thu Jan 18 2018 12:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Wed Jan 17 2018 12:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Wed Jan 17 2018 12:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Sat Jan 20 2018 12:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Sat Jan 20 2018 18:00:00 GMT+0000 (UTC) 
Pune     | 1.234 | 2.3456| Sun Jan 21 2018 12:00:00 GMT+0000 (UTC) 
Pune     | 1.234 | 2.3456| Sun Jan 21 2018 18:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Sun Jan 21 2018 24:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Sun Jan 21 2018 00:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Fri Jan 19 2018 01:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Fri Jan 19 2018 12:00:00 GMT+0000 (UTC)
Pune     | 1.234 | 2.3456| Fri Jan 19 2018 14:00:00 GMT+0000 (UTC)
Ahmadabad| 1.234 | 2.3456| Thu Jan 18 2018 12:00:00 GMT+0000 (UTC)
Ahmadabad| 1.234 | 2.3456| Wed Jan 17 2018 12:00:00 GMT+0000 (UTC)
Ahmadabad| 1.234 | 2.3456| Wed Jan 17 2018 12:00:00 GMT+0000 (UTC)

Now, according to city name I want to update all dates such that, s'pose today is 23rd Jul 2018 and I've selected Mumbai, now I want first fetch all data related to selected city(e.g 'Mumbai') in descending order by date and start updating record by taking yesterday's date i.e. 22nd Jul 2018 for each 4 records in decreasing order of date, such that time stamp will be varying by 6 hours. But remaining column data should not be updated.

Then for city Mumbai if I sort data in descending then I get this

Mumbai   | 1.234 | 2.3456| Sun Jan 21 2018 12:00:00 GMT+0000 (UTC) 
Mumbai   | 2.234 | 2.3456| Sun Jan 21 2018 18:00:00 GMT+0000 (UTC)
Mumbai   | 1.234 | 2.3456| Sun Jan 21 2018 24:00:00 GMT+0000 (UTC)
Mumbai   | 1.234 | 2.3456| Sun Jan 21 2018 00:00:00 GMT+0000 (UTC) 
Mumbai   | 1.234 | 2.3456| Sat Jan 20 2018 12:00:00 GMT+0000 (UTC)
Mumbai   | 6.234 | 2.3456| Sat Jan 20 2018 18:00:00 GMT+0000 (UTC) 
Mumbai   | 8.234 | 2.3456| Fri Jan 19 2018 01:00:00 GMT+0000 (UTC) 
Mumbai   | 3.334 | 2.3456| Fri Jan 19 2018 12:00:00 GMT+0000 (UTC)
Mumbai   | 8.214 | 2.3456| Fri Jan 19 2018 14:00:00 GMT+0000 (UTC)
Mumbai   | 19.234| 2.3456| Thu Jan 18 2018 12:00:00 GMT+0000 (UTC)

Now expected o/p should be

Mumbai   | 1.234 | 2.3456| Sun Jul 22 2018 00:00:00 GMT+0000 (UTC) 
Mumbai   | 2.234 | 2.3456| Sun Jul 22 2018 06:00:00 GMT+0000 (UTC)  
Mumbai   | 1.234 | 2.3456| Sun Jul 22 2018 12:00:00 GMT+0000 (UTC)
Mumbai   | 1.234 | 2.3456| Sun Jul 22 2018 18:00:00 GMT+0000 (UTC) 
Mumbai   | 1.234 | 2.3456| Sat Jul 21 2018 00:00:00 GMT+0000 (UTC)
Mumbai   | 6.234 | 2.3456| Sat Jul 21 2018 06:00:00 GMT+0000 (UTC)  
Mumbai   | 8.234 | 2.3456| Sat Jul 21 2018 12:00:00 GMT+0000 (UTC)
Mumbai   | 3.334 | 2.3456| Sat Jul 21 2018 18:00:00 GMT+0000 (UTC)
Mumbai   | 8.214 | 2.3456| Fri Jul 20 2018 00:00:00 GMT+0000 (UTC)
Mumbai   | 19.234| 2.3456| Fri Jul 20 2018 06:00:00 GMT+0000 (UTC)

My other column data should not be updated, but only dates I want to update, by taking 4 records for same date but different time slot.

Or any middle ware logic will be also accepted(prefer javascript), which doesn't really update data in database but data can be manipulated in between.

Help will be appreciated....!

I'm using HANA db, but same sql query works over here. i'm interested in logic only — Prasanna, Jul 23 '18 at 06:58
so you will update by yesterday but why only 4 rows ? as mumbai city return more rows — Zaynul Abadin Tuhin, Jul 23 '18 at 06:59
Mumabi returns me more than 4 rows data, but i want to update it such way, first 4 slots with yesterday's date, second 4 slots with day before yesterday's date and so on — Prasanna, Jul 23 '18 at 07:05
Thats not *`JSON`*! Please post some part of your json string! — F. Hauri - Give Up GitHub, Sep 02 '18 at 11:15
Do you want to do this in [tag:javascript] or [tag:sql]?? (client side or server side?) — F. Hauri - Give Up GitHub, Sep 02 '18 at 11:19
any side, so that i can implement the same. According to solution — Prasanna, Sep 02 '18 at 11:37

IVO GELOV · Answer 1 · 2018-07-23T09:05:50.013

6

It is very simple - just number the rows and then calculate the time based on the row number (SQLfiddle)

SELECT 
  IF(@city = cityname, @ctr := @ctr + 1, @ctr := 0) AS rownum, 
  @city := cityname AS cityName,
  DATE_SUB(CURRENT_DATE, INTERVAL CEIL((@ctr + 1) / 4) DAY) AS datum,
  SEC_TO_TIME((@ctr % 4) * 21600) AS vreme
FROM tblCity 
JOIN (SELECT @ctr := 0) AS tmp 
ORDER BY cityName,date DESC

EDIT - explanation

(SELECT @ctr := 0) AS tmp initializes a custom session variable with value 0. It will be used to count the number of rows for each city.

Then you get the list of cities and timestamps - sorting the cities in ascending order but timestamps in descending order.

IF(@city = cityname, @ctr := @ctr + 1, @ctr := 0) ensures that the rownumber is reset back to 0 every time you start a new group of rows for the next city - we use the session variable @city to catch the switching.

DATE_SUB(CURRENT_DATE, INTERVAL CEIL((@ctr + 1) / 4) DAY) simply gets the current date and subtracts as many days from it as there are groups of 4 rows for the current city. It will subtract 1 for rows 0/1/2/3, then 2 for rows 4/5/6/7, then 3 for rows 8/9/10/11 and so on.

SEC_TO_TIME((@ctr % 4) * 21600) simply converts from seconds to time in HH:MM:SS format and increments with 6 hours for every row.

edited Jul 23 '18 at 09:05

answered Jul 23 '18 at 08:13

IVO GELOV

13,496
1
17
26

i want it in sql, not in Mysql – Prasanna Jul 23 '18 at 08:50
Use the idea and convert it to ANSI SQL. If you post the result back here - you will get additional points for answering your own question. – IVO GELOV Jul 23 '18 at 08:53
what does @ctr means? can u explain little bit more this.Rest i understood – Prasanna Jul 23 '18 at 08:55
also i want to modify columns in the database, don't want to alias manipulated data. So that i can put ans correct results here – Prasanna Jul 23 '18 at 09:02
For this you will need to add the primary key of the table - so that you can refer to rows by their ID. You will also need to use this query as CTE (common table expression) in order to modify the same table. You may also use window functions instead of session variables. – IVO GELOV Jul 23 '18 at 09:09

LukStorms · Answer 2 · 2018-09-05T22:23:57.477

You could use the window function ROW_NUMBER. And use it calculate the hours or seconds to add to the current date.

But datetime functions differ a bit accross the different DBMS.
So it's hard to write a SQL that would work on any DB.

Here's an example that works on MS Sql Server

select CityName, data1, data2 
, dateadd(hour, 
         (((row_number() over (partition by CityName order by "date")-1)%4)*6)
       -((floor((row_number() over (partition by CityName order by "date")-1)/4)+1)*24),
         cast(cast(current_timestamp as Date) as datetime))  as "date"
from grad as t
where CityName = 'Mumbai'
order by t."date";

Test here

I haven't found a similar online tester for SAP HANA.

But according some documentation it supports window functions. And it has an ADD_SECONDS function.

So perhaps this SQL might work in SAP HANA :

select CityName, data1, data2 
, ADD_SECONDS(
        cast(cast(current_timestamp as Date) as datetime),
        ((mod((row_number() over (partition by CityName order by "date")-1),4)*6)
       -((floor((row_number() over (partition by CityName order by "date")-1)/4)+1)*24))*3600
         )  as "date"
from grad as t
where CityName = 'Mumbai'
order by t."date"

score 1 · Answer 3 · answered Sep 05 '18 at 10:29

/* let the data is stored in the values key of json object i.e */
const jsonData = { values: [
  {
    "cityName": "Mumbai",
    "data1": 1.234,
    "data2": 2.3456,
    "date": "Sat Jan 20 2018 12:00:00 GMT+0000 (UTC)"
  },
  {
    "cityName": "Mumbai",
    "data1": 6.234,
    "data2": 2.3456,
    "date": "Sat Jan 20 2018 18:00:00 GMT+0000 (UTC)"
  },
  ...
]};

/* clone jsonData object into dataObj*/
 const dataObj = Object.assign({}, jsonData);

/* get the array of values from the cloned object i.e. dataObj */
const values = dataObj.values;

/* get name of selected city */
const selectedCity = 'mumbai';

/* find the records of the selected city from the array (convert to lowercase for matching to abort case sensitivity) */
let selectedCityRecords = values.filter(d => d.cityName.toLowerCase() === selectedCity.toLowerCase());

/* sort the records with the descending order of date */
selectedCityRecords.sort((a, b) => {
  var dateA = new Date(a.date);
  var dateB = new Date(b.date)
  if (dateA < dateB) {
    return 1;
  }
  if (dateA > dateB) {
    return -1;
  }

  return 0;
});

let i = 0;
var startDate = new Date();

/* Function to calculate the process date according to slot number */
function getdateTimeSlot(processDate, slotNumber) {
  processDate.setHours(slotNumber * 6, 0, 0);
  return processDate;
}

/* map the selected city data records */
selectedCityRecords.map(data => {
  updatedData = data;
  /* with 6 hours there are 4 slot for a day, after every slot date get decrease by one */
  if (i % 4 === 0) {
    startDate.setDate(startDate.getDate() - 1);
  }
  slotNumber = i % 4;
  newDate = getdateTimeSlot(startDate, slotNumber);
  updatedData.date = newDate.toString();
  i += 1;
  return updatedData;
});

console.log(selectedCityRecords);

Want to update dates in date column, with the past dates from yesterday's date

3 Answers3

EDIT - explanation