Hi,
I’m having trouble fetching data from my database using a Date filter because the database stores dates as ‘2024-02-22 16:41:14.749+05:30’, while the UI accepts dates in 'DD/MM/YYYY HH
’ format like ‘22/02/2024 14:02’.
Kindly look into this.
Thankyou
Hi Rakesh
What database are you using, what are the types of the entity attribute and table column?
And what exactly doesn’t work for you?
Regards,
Konstantin
Hii Konstantin Krivopustov,
I am using postgresql and using Timestamp format to store date in database.
Regards,
Rakesh Panchal
OK, to check the issue we also need your Java attribute type.
Hii Konstantin Krivopustov,
I am using this format to store and fetch date from database in entity.
@LastModifiedDate
@Column(name = "LAST_MODIFIED_DATE")
@Temporal(TemporalType.TIMESTAMP)
private Date lastModifiedDate;
and i am trying also this code
@Install(to = "repositionCasesesDl", target = Target.DATA_LOADER)
private List<RepositionCases> repositionCasesesDlLoadDelegate(final LoadContext<RepositionCases> loadContext) {
List<RepositionCases> datalist = new ArrayList<>();
try {
Condition rootCondition = Objects.requireNonNull(loadContext.getQuery()).getCondition();
List<Condition> conditions = ((LogicalCondition) rootCondition).getConditions();
Date updateDate = null;
for (Condition c : conditions) {
if (c instanceof PropertyCondition) {
Object parameterValue = ((PropertyCondition) c).getParameterValue();
if (((PropertyCondition) c).getProperty().equalsIgnoreCase("lastModifiedDate")) {
updateDate = (Date) parameterValue;
}
}
}
if(updateDate != null) {
int totalRows = repositionCasesesTable.getItems().size() ;
log.info("DataGrid totalRows :: "+totalRows);
String updateDateStr = new SimpleDateFormat("yyyy-MM-dd").format(updateDate);
List<String> listOFVertical = getRolesHierarchy.getVerticalListMappedInHierarchy(String.valueOf(user.getId()));
log.info("ListOfVertical :: " + listOFVertical);
if (listOFVertical.size() > 0) {
if (pendingWithCombo.getValue() != null && pendingWithCombo.getValue().equals("All Cases")) {
datalist = dataManager.load(RepositionCases.class)
.query("select e from RepositionCases e where e.portfolio in :portfolio and"+
" e.deleteFlag = :deleteFlag and FUNCTION('DATE',e.lastModifiedDate) =:date" +
" order by e.lastModifiedDate DESC")
.parameter("portfolio",listOFVertical)
.parameter("deleteFlag","F")
.parameter("date",java.sql.Date.valueOf(updateDateStr))
.list();
} else if (pendingWithCombo.getValue() != null && pendingWithCombo.getValue().equals("Pending With Me")) {
List<String> roleList = new ArrayList<>();
for (int i = 0; i < currentAuthentication.getUser().getAuthorities().size(); i++)
{
roleList.add(((RoleGrantedAuthority) ((ArrayList) currentAuthentication.getUser().getAuthorities()).get(i)).getAuthority());
}
String roleListValues = roleList.stream().map(str -> String.format("'%s'", str)).collect(Collectors.joining(","));
log.info("roleListValues :: " + roleListValues);
Map<String, Object> paramterValues = new HashMap<>();
paramterValues.put("username", user.getUsername());
paramterValues.put("roleList", roleListValues);
paramterValues.put("userId",user.getId());
log.info(" :: parameterValues ::" + paramterValues);
List<String> listOFRepoCases = ruleAllocationService.pendingWithMeCase(paramterValues);
if (roleList.size() > 0) {
datalist = dataManager.load(RepositionCases.class)
.query("select e from RepositionCases e where e.portfolio in :portfolio" +
" and e.id in :id and e.deleteFlag = :deleteFlag " +
"and FUNCTION('DATE',e.lastModifiedDate) =:date " +
"order by e.lastModifiedDate DESC ")
.parameter("portfolio",listOFVertical)
.parameter("id",listOFRepoCases)
.parameter("deleteFlag","F")
.parameter("date",java.sql.Date.valueOf(updateDateStr))
.list();
}
}
}
}
else {
datalist = dataManager.loadList(loadContext);
}
}catch (Exception e)
{
e.printStackTrace();
}
return datalist;
}
i get the expected result but another filters not working with date filter.
Could you please assist in resolving this?
While trying to reproduce your situation, I came across the following problem: Filter by DateTime property doesn't show time part of value · Issue #3452 · jmix-framework/jmix · GitHub
If it’s not related, please describe how exactly you are trying to filter by the property in UI.