Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

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

Tags:

javascript

sql

hana

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....!

like image 770
Prasanna Avatar asked Jul 23 '18 06:07

Prasanna

People also ask

How do you add one day to a date?

const date = new Date(); date. setDate(date. getDate() + 1); // ✅ 1 Day added console.

How do you increment a date object?

To increment a JavaScript date object by one or more days, you can use the combination of setDate() and getDate() methods that are available for any JavaScript Date object instance. The setDate() method allows you to change the date of the date object by passing an integer representing the day of the month.

How can I get yesterday date in SQL query?

To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime ) and cast it to date . In SQL Server, you can subtract or add any number of days using the DATEADD() function. The DATEADD() function takes three arguments: datepart , number , and date .

Video Answer

2 Answers

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.

like image 170
IVO GELOV Avatar answered Oct 03 '22 20:10

IVO GELOV

 
/* 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);
like image 43
Laxmi Saini Avatar answered Oct 03 '22 21:10

Laxmi Saini
Sign in to Comment

Related questions

Implementing a dynamic JSX element within a marker, using react-leaflet
JS - property replacement
React Native ReferenceError Can't find variable
HTML5 - Detect the type of error when trying to load a video using a source element
Align arbitrary number of elements with different widths to a grid with wrapping
Change content of text elements in svg on a node.js server
Variable with reg expression is not recognized in js
Right approach to display success, error messages via NGRX
Firefox extension request is interpreted as CORS
Chrome 65 can't print hidden iframe
How to customize Google Calendar with CSS?
Server Sent Events not working in Chrome
How to export a variable that takes its value from an async function
Style individual Picker.Item in React Native
No \p{L} for JavaScript Regex ? Use Unicode in JS regex [duplicate]
Angular 6 - Importing shared module for child modules
i have error with jquery datetime picker that change the time when i focus out the controll
npm run dev script error
SpyOn a mocked jest module not spying properly
Should MutationObservers be removed/disconnected when the attached DOM node is removed like removeEventListener for events?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!