想要更新日期列中的日期，以及昨天日期的过去日期

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.

这听起来很奇怪的用例,但我想用一些数据更新日期列。请在下表中找到。我正在处理JSON格式的数据。

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.

现在,根据城市名称,我想更新所有日期,今天是2018年7月23日,我选择了孟买,现在我想首先按降序获取与所选城市相关的所有数据(例如'孟买')按日期开始更新记录,按照昨天的日期(即2018年7月22日)按日期递减的顺序每4条记录更新记录,这样时间戳将变化6小时。但不应更新剩余的列数据。

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

现在预计o / p应该是

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.

不应该更新我的其他列数据,而只应更新我想要更新的日期,方法是记录相同日期但不同时间段的4条记录。

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.

或者也可以接受任何中间件逻辑(更喜欢javascript),它不会真正更新数据库中的数据,但可以在两者之间操纵数据。

Help will be appreciated....!

帮助将不胜感激....!

3 个解决方案

#1

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

它非常简单 - 只需对行进行编号,然后根据行号计算时间(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.

(SELECT @ctr:= 0)AS tmp初始化一个值为0的自定义会话变量。它将用于计算每个城市的行数。

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.

IF(@city = cityname,@ ctr:= @ctr + 1,@ ctr:= 0)确保每次为下一个城市开始一组新行时,rownumber将重置为0 - 我们使用会话变量@city赶上转换。

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.

DATE_SUB(CURRENT_DATE,INTERVAL CEIL((@ ctr + 1)/ 4)DAY)只需获取当前日期并从中减去多天,因为当前城市有4行组。对于行0/1/2/3,它将减去1,然后对于行4/5/6/7减去2,然后对于行8/9/10/11减去3,依此类推。

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.

SEC_TO_TIME((@ ctr%4)* 21600)只是以HH:MM:SS格式从秒转换为时间,并且每行增加6小时。

#2

/* 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);

#3

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

您可以使用窗口函数ROW_NUMBER。并使用它计算添加到当前日期的小时数或秒数。

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

但是日期时间函数在不同的DBMS上有所不同。因此很难编写一个适用于任何数据库的SQL。

Here's an example that works on MS Sql Server

这是一个适用于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.

我还没有找到类似的SAP HANA在线测试人员。

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

但根据一些文档,它支持窗口功能。它有一个ADD_SECONDS功能。

So perhaps this SQL might work in SAP HANA :

因此,这个SQL可能适用于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"