Unique Life

입력 날짜를 자동으로 입력되게 하기 위해서 TIMESTAMP 를 사용 했습니다.
그런데 날짜 필드를 수정 하지 않더라도, 다른 정보를 수정하게 되면 같이 변경 되어 버리네요.
필드가 regidate 인데 속성을 TIMESTAMP 로 잡혀 있습니다.
최초 입력시만 자동 등록되고, 수정하더라도 바뀌지 않게 하려면 어떻게 해야 하는지요?

mssql만 주로 하다보니, 몰랐던 부분이라 당황 스럽네요 ㅠㅠ

자답 입니다.
기본값을 current_timestamp  로 변경 하니 되네요.
기본값이 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 로 되어 있더군요.

1. ALTER TABLE `invoices`.`invoices`
    CHANGE `DateTime` `DateTime` TIMESTAMP NOT NULL DEFAULT 0;

2. ALTER TABLE `invoices`.`invoices`
    CHANGE `DateTime` `DateTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

alter table IPS modify time2 timestamp default current_timestamp;

출처: http://ra2kstar.tistory.com/97

http://develop.sunshiny.co.kr/385

## 각각의 캐릭터 셋을 변경 - 이 변경 방법은 mysql 데몬이 재실행 될경우에 초기화 됨.
mysql> SET character_set_client = utf8;
mysql> SET character_set_results = utf8;
mysql> SET character_set_connection = utf8;
mysql> ALTER DATABASE DB명 DEFAULT CHARACTER SET utf8;
commit;

MySQL 기본 패키지는 character set 이 latin1로 설정되어 있다. 따라서 서버에 따라서 세팅을 바꿔줘야 할 필요가 있다. 

나같은 경우에는 UTF-8로 통일하였다. 

우선 현재 mysql의 설정값을 확인해 보자 

역시 latin1으로 되어있다. 

명령어를 통해서도 mysql을 사용할때 변경 할 수 있지만, 이렇게 되면 데몬이 재실행 될때마다 다시 latin1으로 돌아간다. 따라서 고정적으로 바꿔주자. 

  기본 케릭터 셋들을 원하는 값으로 변경 ( UTF-8 로 변경하였다 ) 

mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 7;

그림1

그림1은 unix time을 나타낸다

그림2는 Epoch 시간을 나타내며

unix_timestamp() 함수를 써주면 변환이 된다.

Quick summary

The Mini-Course

Let's first create a simple logging-table and some sample records.
All queries on this page will work with the following table.

copytextpop-up

-- Table "mytable" DDL
 
CREATE TABLE `mytable` (
  `userId` int(11) NOT NULL,
  `url` varchar(100) NOT NULL,
  `epoch` int(11) NOT NULL
);
 
INSERT INTO mytable VALUES(1,'homepage',1225540800); 
# User 1 visited the url homepage on Nov 1, 2008
INSERT INTO mytable VALUES(2,'contact',1225886400); 
# User 2 visited the url contact on Nov 5, 2008
INSERT INTO mytable VALUES(3,'contact',1225972800); 
# User 3 visited the url contact on Nov 6, 2008
INSERT INTO mytable VALUES(4,'homepage',1228132800); 
# User 4 visited the url homepage on Dec 1, 2008

-- Table "mytable" DDL

CREATE TABLE `mytable` (
  `userId` int(11) NOT NULL,
  `url` varchar(100) NOT NULL,
  `epoch` int(11) NOT NULL
);

INSERT INTO mytable VALUES(1,'homepage',1225540800); 
# User 1 visited the url homepage on Nov 1, 2008
INSERT INTO mytable VALUES(2,'contact',1225886400); 
# User 2 visited the url contact on Nov 5, 2008
INSERT INTO mytable VALUES(3,'contact',1225972800); 
# User 3 visited the url contact on Nov 6, 2008
INSERT INTO mytable VALUES(4,'homepage',1228132800); 
# User 4 visited the url homepage on Dec 1, 2008

Converting to readable dates

copytextpop-up

SELECT userId, url, FROM_UNIXTIME(epoch) FROM mytable

SELECT userId, url, FROM_UNIXTIME(epoch) FROM mytable

This query outputs:

1   homepage   2008-11-01 13:00:00
2   contact   2008-11-05 13:00:00
3   contact   2008-11-06 13:00:00
4   homepage   2008-12-01 13:00:00

You can format your date by using specifiers (look below for a list of specifiers).

copytextpop-up

SELECT userId, url, FROM_UNIXTIME(epoch,"%Y-%m-%d") FROM mytable

SELECT userId, url, FROM_UNIXTIME(epoch,"%Y-%m-%d") FROM mytable

Output:

1   homepage   2008-11-01
2   contact   2008-11-05
3   contact   2008-11-06
4   homepage   2008-12-01

Grouping Epochs

Let's say you want to get statistics by month. This query groups months, and counts the number of visitor (records) in each month. We order by epoch to get all results in the right order.

copytextpop-up

SELECT DISTINCT FROM_UNIXTIME(epoch,"%M, %Y") AS month, count(*) as numberOfVisits
FROM mytable
GROUP BY month
ORDER BY epoch

SELECT DISTINCT FROM_UNIXTIME(epoch,"%M, %Y") AS month, count(*) as numberOfVisits
FROM mytable
GROUP BY month
ORDER BY epoch

This outputs:

November, 2008   3
December, 2008   1

This query can be easily changed to get statistics per year, per day, per day of the week, per hour of the day etc. For example, to get yearly stats change the query to:

copytextpop-up

SELECT DISTINCT FROM_UNIXTIME(epoch,"%Y") AS year, count(*) as numberOfVisits
FROM mytable
GROUP BY year
ORDER BY epoch

SELECT DISTINCT FROM_UNIXTIME(epoch,"%Y") AS year, count(*) as numberOfVisits
FROM mytable
GROUP BY year
ORDER BY epoch

Adding a new record to our database

Use the UNIX_TIMESTAMP() function to convert MySQL dates/times (such as now() = current time) to epochs.

copytextpop-up

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP(now()))

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP(now()))

or use YYYY-MM-DD HH:MM:SS :

copytextpop-up

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP('2008-12-01 12:00:00'))

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP('2008-12-01 12:00:00'))

Negative Epochs

There's one big problem with MySQL: MySQL cannot convert negative epoch timestamps (dates before 1-1-1970). This creates problems with for example birthdates. But there are workarounds.

When converting from epoch to human readable date use the DATE_ADD function:

copytextpop-up

# converting to MySQL date:
SELECT DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second);
# converting your epoch to a date string:
SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),'%Y-%m-%d');

# converting to MySQL date:
SELECT DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second);
# converting your epoch to a date string:
SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),'%Y-%m-%d');

Where -315619200 is your negative epoch. This query returns: 1960-01-01 01:00:00

When converting normal dates to epoch use TIMESTAMPDIFF:

copytextpop-up

SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00' );

SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00' );

Replace the 1960 date with your date in your local timezone (MySQL time_zone).

MySQL date format specifiers