mysql 4.0.3 버전에서는 월차, 일차, 년차를 구하는 함수가 없는듯..

그래서 구현된 코드를 구했다. 조 아래...

User Comments

When selecting a timestamp datatype from a table
and want adjust to a timezone (this example is
from pacific time to EST):
SELECT date_format(DATE_ADD([timestampcol],
INTERVAL 3 HOUR), '%M %e, %Y at %H:%i EST') FROM
[db table name];

If you're looking for generic SQL queries that will allow you to get the days, months, and years between any two given dates, you might consider using these. You just need to substitute date1 and date2 with your date expressions.

NOTE: Some of these formulas are complex because they account for all cases where date1 < date2, date1 = date2, and date1 > date2. Additionally, these formulas can be used in very generic queries where aliases and temporary variables are not allowed.


Number of days between date1 and date2:


TO_DAYS(date2) - TO_DAYS(date1)


Number of months between date1 and date2:


IF((((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1))) > 0, (((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1))) - (MID(date2, 9, 2) < MID(date1, 9, 2)), IF((((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1))) < 0, (((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1))) + (MID(date1, 9, 2) < MID(date2, 9, 2)), (((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1)))))


Number of years between date1 and date2:


IF((YEAR(date2) - YEAR(date1)) > 0, (YEAR(date2) - YEAR(date1)) - (MID(date2, 6, 5) < MID(date1, 6, 5)), IF((YEAR(date2) - YEAR(date1)) < 0, (YEAR(date2) - YEAR(date1)) + (MID(date1, 6, 5) < MID(date2, 6, 5)), (YEAR(date2) - YEAR(date1))))


Now for some comments about these.


1. These results return integer number of years, months, and days. They are "floored." Thus, 1.4 days would display as 1 day, and 13.9 years would display as 13 years. Likewise, -1.4 years would display as -1 year, and -13.9 months would display as -13 months.


2. Note that I use boolean expressions in many cases. Because boolean expressions evaluate to 0 or 1, I can use them to subtract or add 1 from the total based on a condition.


For example, to calculate the number of years between to dates, first simply subtract the years. The problem is that doing so isn't always correct. Consider the number of years between July 1, 1950 and May 1, 1952. Technically, there is only one full year between them. On July 1, 1952 and later, there will be two years. Therefore, you should subtract one year in case the date hasn't yet reached a full year. This is done by checking the if the second month-day is before the first month-
day. If so, this results in a value of 1, which is subtracted from the total. The IF statements are in the formula because we must add one year when dealing with the dates in the opposite order, and we must not add or subtract anything when the difference of the date years is zero.


3. To get the month-day, I use MID. This is better
than using RIGHT, since it will work for both dates
and datetimes.


4. Unlike many other solutions, these queries should
work with dates prior to 01/01/1970.

In order to get the number of seconds between two
datetime values in a table, you could use the
following: SELECT unix_timestamp(date1) -
unix_timestamp(date2) FROM table_name

Spent some time trying to work out how to calculate the month start x months ago ( so that I can create historical stats on the fly)

here is what I came up with..

((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1)

this gives you the first day of the month six months before the start of the current month in datetime format

To get the date difference between two date-type columns,
use this formula:

sec_to_time(unix_timestamp(EndDateTime) -
unix_timestamp(StartDateTime))

where StartDateTime and EndDateTime are the two columns

/A

Several times i have come to a followng date/time problem:
In the table i am storing both date and time information in the datetime column. Querying, I want to receive COUNTed results grouped by date, and not date and time. I came to the easy solution:
SELECT DATE_FORMAT(postdate, '%Y-%m-%d') AS dd, COUNT(id) FROM MyTable GROUP BY dd;

I suppose this solution to be quite slow (date formatting).

Later, i 'upgraded' this query to use the string function:
SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd;

knowing, that the result is in the fixed format. Works faster.

Hope this will help somebody. The way I found to sum time:
SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) ) AS total_time FROM time_table;

Comparing Dates when using MS Access and MyODBC

If you are using MS Access and have created Access queries to substitute for views (which are not yet available in mySQL), you can use the following syntax ro perform date comparisons and avoid the dreaded "ODBC -- call failed" error:

Select * from [Task Effort Summary]
Where ((Date() + 0) > CLng([Task Effort Summary].[s_end]))

This particular example retuns tasks that are overdue (where todays date is past the scheduled end date). This query was developed for reports on a TUTOS database.

the unix time stamps may behave weird in mysql:
>select unix_timestamp('0000-00-00 19:01:50');

+---------------------------------------+
| unix_timestamp('0000-00-00 19:01:50') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

+------------------------------------------------------+
| FROM_UNIXTIME(unix_timestamp('0000-00-00 19:01:50')) |
+------------------------------------------------------+
| 1969-12-31 19:00:00                                  |
+------------------------------------------------------+

+------------------------------------------------------+
| FROM_UNIXTIME(unix_timestamp('0000-00-00 18:01:50')) |
+------------------------------------------------------+
| 1969-12-31 19:00:00                                  |
+------------------------------------------------------+

+-------------------------------------------------+
| FROM_UNIXTIME(unix_timestamp('0000-00-00')+360) |
+-------------------------------------------------+
| 1969-12-31 19:06:00                             |
+-------------------------------------------------+

+------------------------------------------------------+
| FROM_UNIXTIME(unix_timestamp('2003-12-08 18:01:50')) |
+------------------------------------------------------+
| 2003-12-08 18:01:50                                  |
+------------------------------------------------------+

Note that the built-in default values for the DATE and DATEFIELD column types is out of range. For example, 0000-00-00 is a valid way of expressing NULL, but if the column is set as NOT NULL, 0000-00-00 is still the default value. This can cause problems with some applications using MySQL.

I was looking for a function to detect if the current week is odd or even. I could not find one so I use this:
MOD((DATE_FORMAT(CURDATE(),"%v")),2)
The output is a '0'(even) or a '1'(odd)

To create a DATETIME of NOW() in UTC without upgrading to 4.1.1, just use:

DATE_ADD( '1970-01-01', INTERVAL UNIX_TIMESTAMP() SECOND )

I am looking for a function to calculate the interval between two records of one column.

workaround for STR_TO_DATE pre version 4.1.1. ugly but it seems to work fine.

assumption: you know the format of the received date (in the below example the format is mm/dd/yy, m/d/yy, mm/dd/yyyy, etc)

the statement extracts the year by locating the index of the second '/' and reading from the right of the string to that index. the index of the second is '/' is found by using LOCATE with the index of the first '/'.
it extracts the day by locating the indeces of the first and second '/' and reading between them
it extracts the month by locating the index of the first '/' and reading from the left of the string to that index.
it then CONCATs the year month and day pieces together separating them with hyphens.
lastly, it lets DATE_FORMAT do its magic on the string.

(replace the test string '1/11/03' with your field name, etc)

select DATE_FORMAT( CONCAT( RIGHT( '1/11/03' , length( '1/11/03') - LOCATE('/', '1/11/03' , LOCATE('/', '1/11/03' ) + 1 ) ) , '-' , LEFT( '1/11/03' , LOCATE('/', '1/11/03' ) - 1 ) , '-', SUBSTRING( '1/11/03' , LOCATE('/', '1/11/03' ) + 1, LOCATE('/', '1/11/03' , LOCATE('/', '1/11/03' ) + 1 ) - LOCATE('/', '1/11/03' ) - 1 ) ) , '%Y-%m-%d' )

Just a really easy way of getting the number of years between to dates:

SELECT ((TO_DAYS(recentdate) - TO-DAYS(olderdate))/365) as tempdate

Will leave a reminder but doesnt take much to clean that up

Lets say you have the mysql before 4.1.1 (where timediff() was implementet), and you want to do a timediff.

I wanted to make a "active users" on my page, but I found out that I didnt have the timediff function (to find persons which have been active within 5 minutes).

So, I figured this query out:

SELECT nick FROM `users` WHERE TO_DAYS( NOW( ) ) - TO_DAYS( last_login ) <=1 AND DATE_FORMAT( CURRENT_TIMESTAMP( ) , '%H%i' ) - DATE_FORMAT( last_login, '%H%i' ) <=5 ORDER BY `nick` ASC;

it selects the field nick (which is the only one to be displayd) and then it filters for 1 day or less in age of activity. after that, it filters for 5 minutes or less in activity.

first you need to filter away the other days, or your script might get fooled to think that yesterdays login was todays.

I'm currently using this, and it works fine!
on the other page, you of course need to update the timestamp field (when session excists, on reload)

Here is an example to convert various user inputs for a date field on an ASP page (VBScript) that will convert common formats (i.e., m/d/yy, mm/dd/yyyy, etc.) to MySQL database format of (yyyy-mm-dd). The function begins by establishing that there is a date in the field. Then splits the date (converted to string) into three parts by locating "/". DateArray(0), DateArray(1), DateArray(2) hold the month, day and year, respectively. These are then checked for the amount of digits, if there are not enough digits in month or day then a leading zero is added. If there are only two digits on the year (ie "04") then a leading "20" is added.

Function ConvertInputDate(varDate)

If (Len(Trim(varDate)) > 0) Then
DateArray=Split(CStr(varDate),"/")

IF Len(Trim(DateArray(0))) < 2 Then
DateArray(0) = "0" & DateArray(0)
End If

If Len(Trim(DateArray(1))) < 2 Then
DateArray(1) = "0" & DateArray(1)
End If

If Len(Trim(DateArray(2))) < 4 Then DateArray(2) = "20" & DateArray(2)
End If

varDate = DateArray(2) & "-" & DateArray(0) & "-" & DateArray(1)

End If

End Function

*Please note if a user does not use two slashes this function will not work. It is best to indicate "mm/dd/yy" near the label on the page. It will take 4/6/04, 10/6/04, 3/16/2004 and all combinations with two slashes.

I had a problem with my login script using PHP and MySQL when daylight savings time (DST) came around this year.

I was using MYSQL NOW() function to add the current date and time to the user's record into a datetime field. When DST came into effect newly entered login times were an hour slow (I'm in EST). Since the last login is to be updated only if an hour or more has passed since the last login this was a big problem!

The problem is that PHP takes DST into account and MySQL does not (as far as I know) and I was entering the time using MySQL's NOW() function and then comparing the value returned by PHP's time() function.

A very simple solution to this is the following. Note the PHP time format string 'YmdHis' - it formats to YYYYMMDDHHMMSS which is what MySQL expects for a date/time field.

$now = time();
$lastLogin = strtotime($row['lastLogin']);
$diff = $now - $lastLogin;
$now = date('YmdHis',$now)

if($diff > 3600) { // 3600 seconds is 1 hour
$query = 'UPDATE members SET logins = logins + 1, lastLogin = '.$now.' WHERE memberID = '.$SEC_ID;
mysql_query($query);
}

Now the date entered is the PHP time (that accounts for DST) and we are comparing it to PHP time so all is well.

I think this approach will work well for any time you wish to enter a date into MySQL using PHP. Just format the date using the "YmdHis" format string and use the strtotime() function to read a date retrieved from MySQL.

The advantage to this approach rather than just entering the "normal" PHP date into a char or text field is that the dates are "human" readable in the table and all the MySQL date/time functions are available for future queries.

to localize the weekday:
SELECT ELT( WEEKDAY('2004-04-10')+1, 'Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag','Sonntag');

long version with month:
SELECT DATE_FORMAT( '2004-04-10', CONCAT( ELT( WEEKDAY('2004-04-10')+1, 'Montag','Dienstag','Mittwoch','Donnerstag','Freitag','Samstag','Sonntag'),', %d. ', ELT( MONTH('2004-04-10'), 'Januar','Februar','M?rz','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember'),' %Y'));
--> Samstag, 10. April 2004

same for unix-timestamp:
SELECT DATE_FORMAT( FROM_UNIXTIME(1081548000), CONCAT( ELT( WEEKDAY(FROM_UNIXTIME(1081548000))+1, 'Mo','Di','Mi','Do','Fr','Sa','So'),', %d. ', ELT( MONTH(FROM_UNIXTIME(1081548000)), 'Jan.','Feb.','M?rz','April','Mai','Juni','Juli','Aug.','Sept.','Okt.','Nov.','Dez.'),' %Y'));
--> Sa, 10. April 2004

I had to query a table and retrieve rows that were added only today, so :

select id from my_table
where
timestamp < date_format(date_add(CURRENT_TIMESTAMP(), interval 1 day),'%Y%m%d000000')
AND
timestamp >= date_format(CURRENT_TIMESTAMP(),'%Y%m%d000000')

starting with MySQL 4.0, you could also use the BETWEEN ... AND syntax.
If anyone has a better query to do that, let me know.

After reading numerous articles and posts regarding converting back and forth between SQL datetime and VBscript datetime, I opted for the simplest solution for my databases. I simply save all datetime values in varchar(20) fields and call on either MySQL or VBscript functions to get datetime values or check/convert datetime values. For example:

currentDT = CStr(cn.execute("SELECT NOW()").Fields(0).Value)

will fetch current datetime in the SQL server's datetime format and then convert it to a string. [Obviously, cn is set by Set cn = Server.CreateObject("ADODB.Connection") to create the database connection, then the database is opened with a cn.open (parameters).]

You can then save this string to an appropriate field such as 'flddate_added' which is formatted as varchar(20).

When retrieving the flddate_added value, you can use this VBscript code to check if the value is indeed a datetime value and convert it to the datetime format of the user's computer"

if IsDate(flddate_added) then
=CDate(flddate_added) ' convert to user's system format for display using user's codepage
else
=flddate_added ' just display the string
end if

The above methods allow me to get around all of the issues regarding VBscript's datetime display format differences depending on the system local.

Some fun with "now()":

CREATE TABLE `test` (
`fecha` VARCHAR( 10 ) NOT NULL
);

INSERT INTO test values (now());

You'll see now you've on field "fecha" today's formatted date.

Regards

David Fern?ndez

Posted by Filip Wolak:

> Several times i have come to a followng date/time problem:
> In the table i am storing both date and time information in the datetime
> column. Querying, I want to receive COUNTed results grouped by date,
> and not date and time.
...
> SELECT substring(postdate, 1,10) ...

If it's a DATETIME column than substring is not appropriate -
it's logically nonsensical of course, and just happens to work
in some version of MySQL because the DATETIME happens
to be represented by a string in some contexts.
Better would be to treat the DATETIME as a DATETIME
rather than as a string, which will work in future versions
of MYSQL and in other RDMS:
SELECT DATE(postdate) ...

Here is another VB/ASP function for converting Dates from standard to MySQL format. Cherise gave a nice example above, but it has extra complexity due to the use of arrays and also may be proned to user input errors.

The following example will work based on the Localization settings of the server on which it is run. So it shouldn't care whether the date is dd-mm-yyyy, mm/dd/yy, mm/dd/yyyy, m-d-yy, etc. Just make sure you pass it a date value that is formatted compliant to the server's localization. If necessary use VB's CDate(strDateValue) before passing strDateValue to the function.

You can also easily modify this function to do the same for Time values, except you use Hour, Minute, and Second VB functions, and delimit with a colon (:) instead of a dash (-).

Hope this helps!

Function funcMySqlDate(dtmChangeDate)
'CONVERTS LOCALIZED DATE FORMAT (for example: m/d/yy) TO MySQL FORMAT (yyyy-mm-dd)
Dim strTempYear, strTempMonth, strTempDay
strTempYear = Year(dtmChangeDate)
strTempMonth = Month(dtmChangeDate)
strTempDay = Day(dtmChangeDate)

if Len(strTempYear) = 2 then 'Y2K TEST - 1938-2037 - ADJUST AS NECESSARY
if strTempYear >= 38 then
strTempYear = "19" & strTempYear
else
strTempYear = "20" & strTempYear
end if
end if
if strTempMonth < 10 then strTempMonth = "0" & strTempMonth
if strTempDay < 10 then strTempDay = "0" & strTempDay

funcMySqlDate = strTempYear & "-" & strTempMonth & "-" & strTempDay
End Function

To find out the last day of a month use:

SELECT (DATE_FORMAT('2004-01-20' ,'%Y-%m-01') - INTERVAL 1 DAY) + INTERVAL 1 MONTH;

It tooks me a few time to have this idea, but it works. If you want to have the first day of a month use:

SELECT DATE_FORMAT('2004-01-20' ,'%Y-%m-01');

To find out the first day of a month was my first development step, then it was easy to extract the last day of a month. It is usefull for accounting for services where I need this solution.

Greetings

I see the use for both, but I find this layout more useful as a reference tool:

+--------------+----------+--------------------+
| metric       | variant  | result             |
+--------------+----------+--------------------+
| microseconds | %f       | 000000..999999     |
| seconds      | %s or %S | 00..59             |
| minutes      | %i       | 00..59             |
| hours        | %H       | 00...23            |
|              | %h or %I | 00...12            |
|              | %k       | 0...23             |
|              | %l       | 1...12             |
| day          | %a       | Sun...Sat          |
|              | %D       | 1st, 2nd, 3rd      |
|              | %d       | 0.31               |
|              | %e       | 0..31              |
|              | %j       | 001...366          |
|              | %W       | Sunday...Sat       |
|              | %w       | 0...6              |
| week         | %U       | 00...53 per Sun    |
|              | %u       | 00...53 per Mon    |
| *            | %V       | 01...53 per Sun    |
| *            | %v       | 01...53 per Mon    |
| month        | %b       | Jan...Dec          |
|              | %c       | 0...12             |
|              | %M       | January...December |
|              | %m       | 00...12            |
| year         | %Y       | 1999               |
|              | %y       | 99                 |
| *            | %X       | 1999               |
| *            | %x       | 99                 |
| time         | %r       | 01:31:12 pm        |
|              |          | %T | 01:31:12 pm   |
|              | %p       | AM or PM           |
| Percent sign | %%       | %                  |
+--------------+----------+--------------------+

Select records that are older than X days from the current date where sent_time is a Timestamp datatype field.

select ID from MESSAGE where SENT_TIME < (CURDATE() - INTERVAL 5 DAY);

GENERATE misssing days on a table with date gaps
=====================================

If you want to bring visits per day to your site and you have a table wich is storing the hits, in a way similar to this...

+--------------+--------------------------+
|  date        | IP                    
+--------------+--------------------------+
|2004-8-3      |  123.123.124.155
|2004-8-3      |  123.123.124.145
|2004-8-5      |  123.123.124.145
+--------------+--------------------------+

+----------+
| date
+----------+
| 2004-1-1
| 2004-1-2
| 2004-1-3
| 2004-1-4
| 2004-1-5
|   ...
| etc...
+---------+

If you do not have 4.xx yet here is a simple way to get the last day of the month. You can replace the current date with a var to find the last day of any month.

SELECT
SUBDATE( ADDDATE( CURDATE(), INTERVAL 1 MONTH), INTERVAL DAYOFMONTH( CURDATE() ) DAY) AS LAST_DAY_MONTH

seems to work well .

I'm not sure if this is mentioned above, however, none of the STR_TO_DATE() functions works in MySQL version 4.0.18.

So that there is no misunderstanding, the following SQL copied from the above STR_TO_DATE() section:

SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');

returns the following error:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('00/00/0000', '%m/%d/%Y')' at line 1

I copied and pasted all of the examples for STR_TO_DATE and none work.

>Several times i have come to a followng date/time problem:
>In the table i am storing both date and time information in the
>datetime column. Querying, I want to receive COUNTed results
>grouped by date, and not date and time. I came to the easy
>solution:

I needed a query for a more general case to do time based reporting on arbitrary big "slices" of timestamped data.

My table has a column 'timestamp' which is of type 'datetime'.

The following makes '120' second big slices

select from_unixtime(unix_timestamp(timestamp) - unix_timestamp(timestamp) % 120) as slice, ... group by slice;