jqGrid DateTime sorting

jquery sorting date jqgrid

12,909

Solution 1

You can specify Am/Pm using A - for example:

datefmt: "m/d/Y h:i A"

The AmPm mentioned in the documentation is the actual object loaded from the language file - but you do not need to worry about those details when defining your formatter.

Solution 2

The comma was not part of the regex string, so that's why it was failing. Taking a look at this git commit fixed the issue.

12,909

Author by

0l33l

Updated on June 04, 2022

Comments

0l33l almost 2 years

How do you sort dates with time in jQGrid? I looked at the documentation which states that I should use the "AmPm" function. However, I still can't get it to sort when taking into account the AM/PM, specifically in the "Login Date" field.

<!DOCTYPE HTML>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>My First Grid</title>
<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/themes/redmond/jquery-ui.css" />
<link rel="stylesheet" type="text/css" href="http://www.ok-soft-gmbh.com/jqGrid/jquery.jqGrid-3.8/css/ui.jqgrid.css" />
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
<script type="text/javascript" src="http://www.ok-soft-gmbh.com/jqGrid/jquery.jqGrid-3.8/js/i18n/grid.locale-en.js"></script>
<script type="text/javascript" src="http://www.ok-soft-gmbh.com/jqGrid/jquery.jqGrid-3.8/js/jquery.jqGrid.min.js"></script>

 <script type="text/javascript"> 
 $.jgrid.no_legacy_api = true; 
 $.jgrid.useJSON = true; 
 </script>  

<script type="text/javascript">
jQuery(document).ready(function(){ 

 jQuery("#list1").jqGrid({
  url: 'login-history.xml',
  datatype: "xml",
  mtype: "GET",
  colNames:["Role","Member Name","Login Date","Logout Date","Duration"],
  colModel:[
   {name:"Role",width:220, xmlmap:"Role"},
   {name:"Member Name",width:120, xmlmap:"MemberFullName"},                 
   {name:"Login Date", width:150,xmlmap:"LoginDateTime", sorttype: "date", datefmt: "m/d/Y h:i AmPm" },
   {name:"Logout Date", width:140,xmlmap:"LogoffDateTime", formatter:'date', formatoptions:{ srcformat:'m/d/Y h:i:s', newformat:'Y-m-d h:i:s' }, sorttype: "date", datefmt: "m/d/Y h:i:s" },
   {name:"Duration", width:80,xmlmap:"DurationInMins", sorttype: "date", datefmt: "h:i"}],
  rowNum:25,
  rowList:[25,50,100,500],
  viewrecords: true,
  loadonce: true,
  sortable: true, 
  altRows:true,
  height: "100%",  
  pager: jQuery('#pager'),
  hidegrid: false,

  xmlReader: {
    root : "DataSet",
    row: "Participant",
    repeatitems: false,
  },
  caption: "Login History"

 }); 
 jQuery("#list1").jqGrid('navGrid','#pager',{add:false,edit:false,del:false});
}); 
</script>

</head>
<body>
    <table id="list1" class="scroll"></table>
    <div id="pager" class="scroll" style="text-align:center;"></div>

</body>
</html>

Sample Data

<?xml version="1.0" encoding="UTF-8"?>
<DataSet>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[46:44]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/30/2010 12:26:55]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/30/2010 11:40 AM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[0:36]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/30/2010 01:40:53]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/30/2010 01:40 PM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[16:39]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/22/2010 01:48:05]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/22/2010 01:31 PM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[11:45]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/21/2010 08:29:02]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/21/2010 08:17 AM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[45:35]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/15/2010 09:25:06]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/15/2010 08:39 AM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[42:18]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/15/2010 12:56:29]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/15/2010 12:14 AM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[5:45]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/15/2010 01:52:43]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/15/2010 01:46 PM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[0:55]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/21/2010 08:16:42]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/21/2010 08:15 AM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[0:49]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/07/2010 01:10:07]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/07/2010 01:09 PM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
  <Participant>
    <Role><![CDATA[Administrator]]></Role>
    <Organization><![CDATA[Test Org]]></Organization>
    <DurationInMins><![CDATA[7:18]]></DurationInMins>
    <LogoffDateTime><![CDATA[09/07/2010 01:17:39]]></LogoffDateTime>
    <LoginDateTime><![CDATA[09/07/2010 01:10 PM, PDT]]></LoginDateTime>
    <MemberFullName><![CDATA[IBM Test]]></MemberFullName>
  </Participant>
</DataSet>

Justin Ethier over 13 years

I just noticed it now, but you may run into trouble with the time zone that is appended to LoginDateTime as the date formatter is not expecting it.
0l33l over 13 years

Thanks Justin. I tried that before, and it does work if I remove the ", PDT". I took another look at the jquery.fmatter.js and figured I should be using datefmt: "m/d/Y h:i A, T". However, this breaks the sorting that works without the time zone. Neither changing "PDT" to "UTC" or "Pacific" does not fix the 11:40 AM coming before 01:40 PM.
0l33l over 13 years

github.com/tonytomov/jqGrid/commit/…
Bhavik Ambani almost 12 years

I want regular expression validation in add form of JQgrid. How can I do that ?