# Guide to handle DateTime values
### Tech Stack under consideration
* DB: **MySQL**
* Backend: **Java Spring-boot**
* Frontend: **Vanilla Javascript**

### Goals
* To store date and time values in such a manner, that it has built in support in the all of the above mentioned tech stacks and can be used/modified/shared with ease.
* Move away from storing datetime in long/bigint
* Store DateTime in such a format that is universal regardless of user's location(time_zone) i.e same when executed in India or America and one that can be transfered back to India's and America's Local time with ease.

The Database picks up time_zone from the server on which it's running which is usually configured to UTC or '+00:00', to be sure the below query can be executed to set it to UTC

SET TIME_ZONE = '+00:00';

In MySQL we will use the DATETIME Datatype to store timestamp values. The TIMESTAMP Datatype should be avoided since it can take values only up to the year 2038.
[TIMESTAMP vs DATETIME](https://www.eversql.com/mysql-datetime-vs-timestamp-column-types-which-one-i-should-use/)
> **NOTE:** if using PostgreSQL, the TIMESTAMP DataType can be used

By Default, DATETIME does not store time in milliseconds, if required the range up to 6 places can be declared inside the parenthesis like DATETIME(6).
CURRENT_TIMESTAMP() method can be used to return the current datetime value

SELECT CURRENT_TIMESTAMP(); --> '2021-09-14 11:18:09'
SELECT CURRENT_TIMESTAMP(5); --> '2021-09-14 11:18:09.98388'

A Table creation(DDL) statement can be created as follows

CREATE TABLE users(
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  created_at DATETIME(5) DEFAULT CURRENT_TIMESTAMP(5),
  updated_at DATETIME(5) DEFAULT CURRENT_TIMESTAMP(5) ON UPDATE CURRENT_TIMESTAMP(5)
);

The ```DEFAULT CURRENT_TIMESTAMP(5)``` will automatically store the current_timestamp when a record is inserted in the table for the first time and ```ON UPDATE CURRENT_TIMESTAMP(5)``` will update the value on each update operation performed on the record.
> DATETIME DataType takes 5 bytes + fractional seconds storage as compared to bigint which takes 8 bytes

By storing datetime values in the DATETIME datatype rather than on a primitive datatype, we will be able to use built in native MySQL functions without any workaround.
[Datetime functions Reference sheet](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html)

---
#### Mapping DATETIME function in Java
In Java, LocalDate and LocalDateTime in java.time package can be used to map columns with DATE and DATETIME declarations respectively.
> Spring Data JPA/Hibernate provide built in support for these and many more new time based datatypes in their latest versions.

@Column(name = "created_at", nullable = false, insertable = false, updatable = false)
private LocalDateTime createdAt;

@Column(name = "updated_at", nullable = false, insertable = false, updatable = false)
private LocalDateTime updatedAt;

* LocalDate and LocalDateTime can be declared in the DTO classes as well, Spring-boot will automatically map the corresponding string value recieved in the RequestBody to the required type.
* If timestamp is required in Path Variable, then declare path variable of type **String** and map it to LocalDate(Time) by following the below mentioned step.

LocalDate.parse("2021-09-14");
LocalDateTime.parse("2021-09-14T11:18:09.98388Z", DateTimeFormatter.ISO_DATE_TIME);

* To send LocalDateTime and LocalDate to DB, execute the below mentioned statements

LocalDate.now(ZoneId.of("+00:00"));
LocalDate.of('1999-12-25').atZone(ZoneId.of("+00:00"));

LocalDateTime.now(ZoneId.of("+00:00"));
LocalDateTime.of(2001, 05, 14, 04, 30, 300).atZone(ZoneId.of("+00:00"));

* LocalDate and LocalDateTime has built in utility methods like isAfter(), isBefore(), isEqual(), plusDays(), plusHours(), minusMinutes() and so on that can come in handy to perform datetime operations.
---

#### When User's Local TimeZone (Offset from UTC) is required in the backend(Java) for processing

Avoid using **TimeZone** and **RequestContextUtils.getTimeZone(httpServletRequest)** since they return servers time_zone rather than user's browser time_zone.

The Above approach will work locally on your machine (127.0.0.1) but will fail when deployed on a production/test server.

[AVOID THIS. (Reference Article link to above approach)](https://www.logicbig.com/tutorials/spring-framework/spring-web-mvc/client-time-zone.html)

**Instead,** take user's timezone from the frontend application in the form of pathVariable or RequestParameter or RequestBody. The Below code snippet is to be run in javascript to capture the time offset in minutes.

-(new Date().getTimezoneOffset());
> 330 // is in minutes (+05:30 hours from UTC)

The sign of .getTimezoneOffset() is reversed because

> The time-zone offset is the difference, in minutes, between UTC and local time. Note that this means that the offset is positive if the local timezone is behind UTC and negative if it is ahead.

Add this offset value to the LocalDateTime object to get the dateTime according to the user's local timezone

appointment.getCreatedAt().plusMinutes(offsetMinutes);

final Boolean appointmentExistsForToday = appointmentRepository.findByUserId(loggedInUsersId).stream().filter(appointment -> appointment.getCreatedAt.plusMinutes(offsetMinutesReceivedFromFrontend).toLocalDate().isEqual(LocalDate.now(ZoneId.of("+00:00").plusMinutes(offsetMinutesReceivedFromFrontend)))).collect(Collectors.toList()).size!=0;

---
#### Communicating with Java From JavaScript using same DataTypes
* To send Current DateTime (LocalDateTime)

new Date().toISOString(); 
> "2021-09-15T02:35:13.143Z"

* To send specific DateTime (LocalDateTime): 25 Dec,1999 4:00 AM

new Date(new Date(1999,11,25).setHours(04,00)).toISOString();
> "1999-12-24T22:30:00.000Z"

* To Display DateTime (LocalDateTime) recieved from server

new Date(string-received-goes-here);

new Date('1999-12-24T22:30:00.000Z');
> Sat Dec 25 1999 04:00:00 GMT+0530 (IST)