Showing posts with label Mysql DATE_FORMAT. Show all posts
Showing posts with label Mysql DATE_FORMAT. Show all posts

Tuesday, 30 July 2019

Using Unix Timestamps in MySQL

This page gives you information on how to easily use Unix Timestamps in MySQL.

Quick summary

GoalMySQL query
Get current epoch timeSELECT UNIX_TIMESTAMP(NOW()) (now() is optional)
Today midnightSELECT UNIX_TIMESTAMP(CURDATE())
Yesterday midnightSELECT UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -1 DAY))
Jan 1 of current yearSELECT UNIX_TIMESTAMP(CONCAT(YEAR(CURDATE()),'-01-01'))
Convert from date to epochSELECT UNIX_TIMESTAMP(timestring)
Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD
Convert from epoch to dateSELECT FROM_UNIXTIME(epoch timestamp, optional output format)
The default output is YYYY-MM-DD HH:MM:SS
FROM_UNIXTIME doesn't work with negative timestamps

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.
-- 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

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).
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 visitors (records) in each month. We order by epoch to get all results in the right order.
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:
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.
INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP(now()))
or use YYYY-MM-DD HH:MM:SS :
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:
-- 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:
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

Specifier Description
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week
%uWeek (00..53), where Monday is the first day of the week
%VWeek (01..53), where Sunday is the first day of the week; used with %X
%vWeek (01..53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal '%' character

Monday, 24 September 2018

Mysql date format

The DATE_FORMAT() function is used to display date/time data in different formats.

Syntax

DATE_FORMAT(date,format)
Where date is a valid date and format specifies the output format for the date/time.
The formats that can be used are from http://www.w3schools.com/sql/func_date_format.asp:

FormatDescription
%aAbbreviated weekday name
%bAbbreviated month name
%cMonth, numeric
%DDay of month with English suffix
%dDay of month, numeric (00-31)
%eDay of month, numeric (0-31)
%fMicroseconds
%HHour (00-23)
%hHour (01-12)
%IHour (01-12)
%iMinutes, numeric (00-59)
%jDay of year (001-366)
%kHour (0-23)
%lHour (1-12)
%MMonth name
%mMonth, numeric (00-12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss AM or PM)
%SSeconds (00-59)
%sSeconds (00-59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00-53) where Sunday is the first day of week
%uWeek (00-53) where Monday is the first day of week
%VWeek (01-53) where Sunday is the first day of week, used with %X
%vWeek (01-53) where Monday is the first day of week, used with %x
%WWeekday name
%wDay of the week (0=Sunday, 6=Saturday)
%XYear of the week where Sunday is the first day of week, four digits, used with %V
%xYear of the week where Monday is the first day of week, four digits, used with %v
%YYear, four digits
%yYear, two digits

Examples in fiddle:

http://sqlfiddle.com/#!2/ccc08/3
















Monday, 3 September 2018

I convert Date in the mysql query does not work with str_to_date?

I am converting Date in Mysql query. It's saved in varchar in database in this format 10-January-2015. I am converting it to year-month-date 2015-01-10. I have used str_to_date(myfield, '%d-%M-%Y'), and date_format(myfield, '%d-%M-%Y'), but it shows no rows.

The query I'm writing shows no rows yet it works when I give exact date in string.
SELECT * FROM table where str_to_date(date, '%d-%M-%Y') = '20-June-2015';
use mysql Date_Format
Date_Format(date, '%d-%M-%Y')
use query like taht:-
SELECT * FROM table where Date_Format(date, '%d-%M-%Y') = '20-June-2015';
Or use str_to_date like that :-
SELECT * FROM table where date= str_to_date('20-June-2015', '%d-%M-%Y')

Tuesday, 28 August 2018

Query mysql does not return results

I am executing this query

SELECT *
FROM temp
WHERE DATE_FORMAT(startTime,'%m/%d/%Y') = '7/15/2012'
and startTime column has this value '2012-07-15 12:00:00'
But this is not returning any results. Can somebody please help?
Change here:
7/15/2012
to:
07/15/2012

Subscribe to: Posts (Atom)