0

I have database that contains utc seconds. I need to write a mysql query for day wise filter, hour wise filter and weekend, weekday filter.

Deepu
  • 83
  • 9
  • Provide any sample .. What is your input and output – sasi Dec 26 '15 at 07:14
  • Input like this "1451114203" and out would be any format of time like weekday or hour. I need to write a mysql query for day wise filters. – Deepu Dec 26 '15 at 07:18

2 Answers2

0

Please Check this..

Date date = new Date(1451114203L);
            DateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
            format.setTimeZone(TimeZone.getTimeZone("Asia/Calcutta"));
            String formatted = format.format(date);
            System.out.println(formatted);

with MYSQL

SELECT from_unixtime(1451114203) ;
SELECT from_unixtime(1451114203, '%Y %D %M %h:%i:%s %a') ;

ref Convert Unix timestamp into human readable date using MySQL

Community
  • 1
  • 1
sasi
  • 4,192
  • 4
  • 28
  • 47
  • this is java conversion. I need to convert that date on the fly in mysql query only. – Deepu Dec 26 '15 at 08:21
  • there is no timezone specific conversions, i need to timezone specific weekday name like monday tuesday based on user current timezone. – Deepu Dec 26 '15 at 09:06
  • Please refer api to make it timezone specific. – sasi Dec 26 '15 at 09:23
0

SELECT * FROM tablename where FROM_UNIXTIME(UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME((1451114203)),'+05:30','-07:00')),'%W') = 'monday'

Here '+05:30' is the server timezone and '-07:00' is the user timezone. I am converting to user timezone. it will result weekday name of user specific timezone. It may be a long and lengthy process I am looking for better performance query.

above query will work with utcseconds only i am not guarantee for time stamps, I need to work on timestamp.

Deepu
  • 83
  • 9