Some time ago, the project involved the conversion between MySQL and MSSQL data types. Recently, I was studying Sina Weibo's API, which involved the conversion of GMT time types with time zones, this is a special record for future query.
I. Conversion between Unix Timestamp and datetime time
1. Convert UNIX timestamp to datetime type time
Method 1:
/// <Summary> /// Convert UNIX timestamp to datetime type time /// </Summary> /// Http://www.cnblogs.com/babycool /// <Param name = "D"> </param> /// <Returns> </returns> Public Static Datetime convertinttodatetime ( Double D) {system. datetime time = System. datetime. minvalue; system. datetime starttime = Timezone. currenttimezone. tolocaltime ( New System. datetime ( 1970 , 1 , 1 ); Time =Starttime. addseconds (d ); Return Time ;}
Method 2:
/// <Summary> /// Convert UNIX timestamp to datetime type time /// </Summary> /// Http://www.cnblogs.com/babycool /// <Param name = "time"> </param> /// <Returns> </returns> Static Datetime convinttodatetime ( Long Time) {datetime Timestamp = New Datetime ( 1970 , 1 , 1 ); // Obtain the timestamp of January 1, 1970. Long T = (Time + 8 * 60 *60 )* 10000000 + Timestamp. ticks; datetime dt = New Datetime (t ); Return DT ;}
2. query and convert in SQL Server Management studio:
--Convert UNIX timestamp to Dateline typeSelect Top 10Dateadd(Ss, regdate,'00:00:00')FromDBO. uc_members
3. Convert datetime to Unix Timestamp
/// <Summary> /// Convert datetime to Unix timestamp format /// </Summary> /// Http://www.cnblogs.com/babycool /// <Param name = "time"> </param> /// <Returns> </returns> Public Static Double Convertdatetimetoint (system. datetime time ){ Double Intresult = 0 ; System. datetime starttime = Timezone. currenttimezone. tolocaltime ( New System. datetime ( 1970 , 1 , 1 ); Intresult = (Time- Starttime). totalseconds; Return Intresult ;}
Ii. Convert the GMT time with Time Zone in Sina Weibo to datetime
/// <Summary> /// Convert the GMT time with Time Zone in Sina Weibo to datetime /// </Summary> /// Http://www.cnblogs.com/babycool /// <Param name = "datestring"> Weibo time string </Param> /// <Returns> Datetime </Returns> Public Static Datetime parseutcdate ( String Datestring) {system. Globalization. cultureinfo provider = System. Globalization. cultureinfo. invariantculture; datetime dt = Datetime. parseexact (datestring, " Ddd Mmm dd hh: mm: ss zzz yyyy " , Provider ); Return DT ;}
Iii. Conversion effect:
CorrespondingCode:
// The return time of Sina Weibo is the GMT time with the time zone, which is converted to the datetime type time format: // GMT time: Tue May 31 17:46:55 + 0800 2011 Response. Write ( " Convert GMT time to datetime type time: " ); Response. Write (parseutcdate ( " Tue May 31 17:46:55 + 0800 2011 " ). Tostring (); response. Write ( " <Br/> " ); // UNIX timestamp 1176686120 Response. Write ( " Convert UNIX timestamp to datetime type time: " ); Response. Write (convertinttodatetime ( 1176686120 ); Response. Write ( " <Br/> " ); Response. Write ( " Method 2: " ); Response. Write (convinttodatetime ( 1176686120 ); Response. Write ( " <Br/> " ); Response. Write ( " Convert datetime to Unix timestamp: " ); Response. Write (convertdatetimetoint (convertinttodatetime ( 1176686120 ); Response. Write ( " <Br/> " );
Indicate the source for reprinting.