MySQL datetime type generates JavaScript date object on the Web end

The server reads the comment table and returns the result set to the caller. The caller converts the result set to a JSON string list using JSON. dumps and returns it to the Web Front-end. After the datetime field dumps, it becomes a string describing the date. For example, '2017-4-1 12:31:01 'JS cannot operate on this date, for example, adding or subtracting n days or N hours based on this time, for example, compare two times. Find the earlier time, so the server first converts datetime to bigint and then transmits it to the client (if the date is the timestamp type of MySQL, the conversion is not required, because timestamp is bigint)

 #  Webpy framework  
  Import Web, time, datetime
 Def Getlist (self, DB, noteid ):
 Try :
SQL = "  Select * from comment where noteid = $ noteid  " 
Rs = dB. Query (SQL, vars = locals (). List ()
 For R In RS:
 If R. posttime = none:
R. posttime = 0
 Continue 
R. posttime = int (time. mktime (R. posttime. timetuple ()))
D = time. localtime (R. posttime)
S = time. strftime ( '  % Y-% m-% d % H: % m: % s  ' , D)
 Return RS
 Except :
 Raise 

 

The date object constructor of JS has multiple overloaded versions. One version can generate a date object based on the time Description of the int type (the number of microseconds since January 1, 0000, 0 is 1970 08:00:00 ), however, datetime is stored in seconds after 1970, so it is also required during initialization.* 1000.

The server uses the Standard Time Zone. The client also needs to correct the server time according to the local time zone. For example, a comment is submitted to the server at Beijing time, this time is the standard time, so the server stores the time, and reading back from the server is not the local time when the user submits it, the user will feel strange, therefore, the Web Client uses js to read the user's time zone and correct the result based on the difference between the user's time zone and the standard time zone. For example, Beijing time requires more than 8 hours of database time, the local time when the user submits the data.

 /*  
Javascript allows you to extend the standard library object through prototype. This function adds a format Method to the date object. This method returns a string based on the time format described by the user parameter.
VaR d = new date ();
S = D. Format ("yyyy-mm-dd hh: mm: SS ");
Alert (s );
  */ 

Date. Prototype. format =Function (FMT ){
 VaR O = {
"M + ": This . Getmonth () + 1, //  Month  
 "D + ": This . Getdate (), //  Day  
 "H + ": This . Gethours () % 12 = 0? 12: This . Gethours () % 12,//  Hours  
 "H + ": This . Gethours (), //  Hours  
 "M + ": This . Getminutes (), //  Minute  
 "S + ": This . Getseconds (), //  Seconds 
 "Q +": Math. Floor (( This . Getmonth () + 3)/3), // quarter
"S ": This . Getmilliseconds () //  Millisecond  
 };
 VaR Week = {
"0": "\ u65e5 ",
"1": "\ u4e00 ",
"2": "\ u4e8c ",
"3": "\ u4e09 ",
"4": "\ u56db ",
"5": "\ u4e94 ",
"6": "\ u516d"
};
 If (/(Y +)/. Test (FMT )){
FMt = FMT. Replace (Regexp. $1 ,( This . Getfullyear () + ""). substr (4-Regexp. $1. Length ));
}
 If (/(E +)/. Test (FMT )){
FMt = FMT. Replace (Regexp. $1, (Regexp. $1. length> 1 )? (Regexp. $1. length> 2? "\ U661f \ u671f": "\ u5468"): "") + week [ This . Getday () + ""]);
}
 For (VaR K In O ){
 If ( New Regexp ("(" + K + ")"). Test (FMT )){
FMt = FMT. Replace (Regexp. $1, (Regexp. $1. Length = 1 )? (O [k]): ("00" + O [k]). substr ("" + O [k]). Length )));
}
}
 Return FMT;
}


 Function Convertutctolocaltime (servertime)
{
VaR D = New Date ();
 //  Gettimezoneoffset () can be used to obtain the minute difference between the time zone of the JS runtime environment and the Standard Time Zone  
      VaR Localzone = D. gettimezoneoffset ();
Servertime = parseint (servertime)
 If (Servertime = 0)
 Return ""

 If (Localzone = 0)
{
 // It takes * 1000 seconds to change the server storage time to microseconds.  
          Return   New Date (parseint (servertime) * 1000). Format ("yyyy-mm-dd hh: mm: SS ")
}

 If (Localzone <0)
{
Localzone = math. Abs (localzone );
Localtime = New Date (parseint (servertime) * 1000). valueof ();
 //  Standard Time + (1000*60 is 1 minute * deviation minutes) = Local Time of the JS Environment  
 Localtime = localtime + (1000*60 * localzone );
 Return   New Date (localtime). Format ("yyyy-mm-dd hh: mm: SS ")
}

 If (Localzone> 0)
{
Localzone = math. Abs (localzone );
Localtime = New Date (parseint (servertime) * 1000). valueof ();
 //  Standard Time-(1000*60 is 1 minute * deviation minutes) = Local Time of the JS Environment  
 Localtime = localtime-(1000*60 * localzone );
 Return   New Date (localtime). Format ("yyyy-mm-dd hh: mm: SS ")
}

 Return "Tntime. JS: convertutctolocaltime has error"
}