7/9/14

MSsql (sql server) convert Datetime / Timestamp into Unix timestamp (bigint)

select CURRENT_TIMESTAMP;

select CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119', CURRENT_TIMESTAMP)) ;

select DateAdd(SECOND, CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119', CURRENT_TIMESTAMP)), '1970-01-01T00:00:00.000') AS TIME_MS;


convert from unix time to sql server datetime:
SELECT DATEADD(s, mycolumn / 1000, '19700101') from mytable
 
It would seem that we could use "ms" instead of the "s" parameter and avoid division by 1000, since the data are stored in milliseconds in mycolumn. However, that created an overflow condition. My only guess is that the dataadd function uses integer math internally instead of bigint.
Next up, how to convert the other direction:
SELECT cast(DATEDIFF(s, '19700101', cast('2012-10-10 14:05:00.000' as datetime)) as bigint) * 1000 from mytable
 
You'll want to replace '2012-10-10 14:05:00.000' with your own date/time string. Again we have this overflow problem if we try to get milliseconds directly in datediff, so we have to multiply by 1000.
This is all well and fine, but my data was not in the GMT timezone. So I additionally need to convert through to my timezone (CST).
SELECT cast(DATEDIFF(s, '19700101', dateadd(hh, 5, cast('2012-10-10 14:05:55.000' as datetime))) as bigint) * 1000 from mytable
 
As before, replace '2012-10-10 14:05:55.000' with your own date/time string.



SQL SERVER – Few Useful DateTime Functions to Find Specific Dates


Recently I have recieved email from 
----TodaySELECT GETDATE() 'Today'----YesterdaySELECT DATEADD(d,-1,GETDATE()) 'Yesterday'----First Day of Current WeekSELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)'First Day of Current Week'----Last Day of Current WeekSELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)'Last Day of Current Week'----First Day of Last WeekSELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)'First Day of Last Week'----Last Day of Last WeekSELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)'Last Day of Last Week'----First Day of Current MonthSELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)'First Day of Current Month'----Last Day of Current MonthSELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))'Last Day of Current Month'----First Day of Last MonthSELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))'First Day of Last Month'----Last Day of Last MonthSELECTDATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))'Last Day of Last Month'----First Day of Current YearSELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)'First Day of Current Year'----Last Day of Current YearSELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))'Last Day of Current Year'----First Day of Last YearSELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))'First Day of Last Year'----Last Day of Last YearSELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))'Last Day of Last Year'
ResultSet:
Today
———————–
2008-08-29 21:54:58.967
Yesterday
———————–
2008-08-28 21:54:58.967
First Day of Current Week
————————-
2008-08-25 00:00:00.000
Last Day of Current Week
————————
2008-08-31 00:00:00.000
First Day of Last Week
———————–
2008-08-18 00:00:00.000
Last Day of Last Week
———————–
2008-08-24 00:00:00.000
First Day of Current Month
————————–
2008-08-01 00:00:00.000
Last Day of Current Month
————————-
2008-08-31 23:59:59.997
First Day of Last Month
———————–
2008-07-01 00:00:00.000
Last Day of Last Month
———————–
2008-07-31 23:59:59.997
First Day of Current Year
————————-
2008-01-01 00:00:00.000
Last Day of Current Year
————————
2008-12-31 23:59:59.997
First Day of Last Year
———————–
2007-01-01 00:00:00.000
Last Day of Last Year
———————–
2007-12-31 23:59:59.997
Reference : Pinal Dave (http://blog.SQLAuthority.com), 

4/10/14

SQL SERVER – Find Row Count in Table – Find Largest Table in Database.

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC
More:

to get all tables in a database:
select * from INFORMATION_SCHEMA.TABLES
to get all columns in a database:
select * from INFORMATION_SCHEMA.columns
to get all views in a db:
select * from INFORMATION_SCHEMA.TABLES where table_type = 'view'

3/19/14

How to enable fingerprint device for Thinkpad L430 in Ubuntu 13.10

Supported readers (run command lsusb to find out the ID of your reader)
============
  045e:00bb        061a:0110        147e:1002
  045e:00bc        08ff:1600        147e:1003
  045e:00bd        08ff:2500        147e:2015
  045e:00ca        08ff:2580        147e:2016
  0483:2015        08ff:5501        147e:3000
  0483:2016        138a:0001        147e:3001
  05ba:0007        147e:1000        147e:5002
  05ba:0008        147e:1001        147e:5003
  05ba:000a

Installation

  1. First of all, if you have installed Fingerprint GUI manually before, get rid of it completely. Remove all binaries, shared libraries, any other files and undo all the changes you have made to your system config files (especially to files under /etc/pam.d/).
  2. Add this PPA to your sources:
    sudo add-apt-repository ppa:fingerprint/fingerprint-gui
    sudo apt-get update
    
  3. Install the packages:
    sudo apt-get install libbsapi policykit-1-fingerprint-gui fingerprint-gui
    
  4. Log out of your session and log back in (we need the new session defaults to be picked up).

Setup

After installation launch Fingerprint GUI (can be found under System > Preferences) and enrol your fingerprints.
That should be all you need to do!
Try locking your screen, logging out and in, sudo in terminal and running graphical apps requiring root privileges.

3/4/14

Removing accents (and diacritics) in any language from Java

Removing accents (and diacritics) in any language from Java


import java.text.Normalizer;
import java.text.Normalizer.Form;

// ...

public static String removeAccents(String text) {
    return text == null ? null
        : Normalizer.normalize(text, Form.NFD)
            .replaceAll("\\p{InCombiningDiacriticalMarks}+", "");
}

or 

 public static String deAccent(String str) {
     String nfdNormalizedString = Normalizer.normalize(str, Normalizer.Form.NFD); 
     Pattern pattern = Pattern.compile("\\p{InCombiningDiacriticalMarks}+");
     return pattern.matcher(nfdNormalizedString).replaceAll(""); 
        }