在mysql數(shù)據(jù)庫中關(guān)于日期時(shí)間字段的處理

在開發(fā)中，日期時(shí)間字段一般有如下幾種設(shè)計(jì)

假設(shè)要獲取2013-08-15日到2013-08-16日之間的記錄

1. 直接使用日期時(shí)間類字段

相關(guān)sql語句如下

select * from cms_news where

?news_add_time between str_to_date("2013-08-15 00:00:00",'%Y-%m-%d %H:%i:%s') and str_to_date("2013-08-16 23:59:59",'%Y-%m-%d %H:%i:%s')

order by news_add_time desc limit 1000

?

2. 使用long型的數(shù)字表示時(shí)間

select * from cms_news where

?news_add_time>=1376496000000 and? news_add_time<=1376668799000

order by news_add_time desc limit 1000

?

    package com.yanek.test;



public class DateTool {



	/**

	 * @param args

	 */

	public static void main(String[] args) {

		

		

		String startDate="2013-08-15";

		String enddate="2013-08-16";

		

		String s1="2013-08-27 00:00:00";

		String s2="2013-08-27 23:59:59";

		String sql = "select * from cms_news where news_time between str_to_date(\""+s1+"\",'%Y-%m-%d %H:%i:%s') and str_to_date(\""+s2+"\",'%Y-%m-%d %H:%i:%s') order by news_time asc limit 1000";

		

		long st1=1376496000000l;

		long st2=1376668799000l;

		String sql1 = "SELECT * FROM cms_news WHERE news_time >=" + st1 + " and lastmodified<="+st2+" ORDER BY news_time ASC LIMIT 100";

		

	}



}


  

?

?

在mysql數(shù)據(jù)庫中關(guān)于日期時(shí)間字段的處理