All Questions
Tagged with sql-date-functions mysql
57 questions
0
votes
2
answers
57
views
How to load last 7 days into a string(list)
I'm trying to create a program that watches and searches for any logs within given dates.
How can I load the last 7 days in DATE format (YYYY-MM-DD) to a string/string list, or via SQL, or Qt/C++.
...
1
vote
1
answer
91
views
SQL behaviour when using string literal vs using date literal
Suppose I have this two queries:
SELECT * FROM payments WHERE created < DATE '1967-01-01'
SELECT * FROM payments WHERE created < '1967-01-01'
And created field is a date field.
I would like to ...
- 15
-1
votes
1
answer
51
views
Why does subtracting NOW with DATE column give nonsense numbers?
Hello everyone,
i have a problem with the subtracting a DATETIME column with todays DATETIME.
One of the columns has the HireDate (Date of hiring a certain employee). I want to know how many Years ...
- 21
-1
votes
1
answer
59
views
Count of Employee only when first_stamp is between a window of start_dt
I want a count of emp_id only when firststamp date is in a 21 day window depending on startdt.
However, I want to count that empid, only when the firststamp either 7 days before the startdt, during ...
- 23
0
votes
1
answer
38
views
Is there a way for me to specify the date function in MySQLWorkBench so that I won’t get an error message?
I tried to create a table, this is how I set it up:
CREATE TABLE emp_tab
(
empno NUMeric(10),
name VARCHAR(50) NOT NULL,
job VARCHAR(50),
manager ...
-1
votes
1
answer
2k
views
Can we use arithmetic operators (+, -. *, or /) on date functions in mysql?
I am learning mysql and I am stuck in the following question.
Can we use arithmetic operators (+, -. *, or /) on date functions in mysql?
Can someone help me out with it?
- 277
1
vote
1
answer
1k
views
Getting average of count(column) grouped by date, but meeting certain conditions
Data sample:
dtime
id
2021-01-01 06:00:00
1
2021-01-01 06:00:00
2
2021-01-01 06:00:00
3
...
...
2021-01-01 12:00:00
1
2021-01-01 12:00:00
2
2021-01-01 12:00:00
3
...
...
...
...
2021-01-12 ...
- 75
-1
votes
1
answer
151
views
Taking month and year from column and showing last date of the month and year
So i have 2 column Year and Month and I want to take the month and the year to show in the new column and showing the last date of the month
Example :
Year column = 2019, 2020, 2021
Month Column = Jan,...
- 1
1
vote
2
answers
2k
views
Retrieve MySql Records Within a Specific Month but Less than the current year and Less than the current Day
I have a number of MySQL records with datetime records. Lets just say they are in a table called my_table and they include the following records.
ID Date
1 2021-04-02
2 2020-04-13
3 ...
- 1,019
-3
votes
2
answers
820
views
how to add 30 min to date values in sql so they change correctly?
there is a timepair table. It has columns :
start_pair end_pair
08:30:00 09:15:00
I need to shift it by 30 min - how to do it? 8:30 becomes 9:00.
update
set start_pair = date(start_pair) + minute(...
- 24.4k
0
votes
1
answer
167
views
Showing a row for every minute based on start and end time
I have a table that looks like below:
task_id start_date end_date
t1 2020-05-01 8:00:00 2020-05-01 9:45:00
t2 2020-05-01 8:30:00 2020-05-01 9:00:00
...
-2
votes
3
answers
223
views
How to find data for last 20 tuesdays
I want to find data for the last 20 Tuesday.
Date value
2020-03-03 01:12:15 5
2020-02-25 07:12:15 13
2020-02-24 08:12:15 1
2020-02-...
- 379
0
votes
1
answer
104
views
Check if dayname of the date is in the dayname column data
I am trying to get the where clause condition to check dayname is in the list of column data.
For example, if today is Monday and the database column has 'Monday, Tuesday', then return True.
ID | ...
- 1
-1
votes
2
answers
108
views
SQL query: A view which gathers from today last month and 2 month in advance
I'm amending a current query which I run on a fairly regular basis for a membership team looking at recent expiries. The clause in that query is:
and date_expiry between '2019-11-01' and '2019-12-31'
...
- 31
0
votes
1
answer
120
views
Find employees who haven’t worked between certain times based on ‘clock ins’ and ‘clock outs’
I have a “clock in” table for employees with attributes start time and end time.
Each employee clocks in and clocks out when they start and finish work and the table is similar to this:
Employee_no ...
user12326227
2
votes
2
answers
62
views
Response time of two messages in the same chat
The scenario
A chat_channel can have multiple MESSAGE_ID which is displayed on separate rows.
INSERT INTO messages_tbl
(message_id,
chat_channel,
user_type,...
- 55
0
votes
0
answers
162
views
Delete from Mysql table where datetime is older than datetime of another item
I have some difficulty to get my MySQL query right. The current status is like this:
I have a table containing Todo's with a datetime field changed_at (set to current datetime on insert/update)
I ...
- 282
3
votes
3
answers
14k
views
Need to subtract some hours from given timestamp in hive
Input: unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')
Expected output is 4 hours delay from above utc input time i.e 01/15/2018 11:26:37
I know that there is date_sub function in hive ...
- 111
1
vote
1
answer
5k
views
How to calculate age based on date of birth in mysql?
My date of birth format is : 2017-Feb-15
when I am using the following query, it's not working
select (year(current_date)-year(dob)) as age from user
or how to current 2017-Feb-15 into 2017-02-15 ...
- 253
1
vote
0
answers
109
views
Insert into table by filtering date from another table?
As a overview we are trying to analyse one kind of eCommerce delivery business, where our today's business depends on last evening and today's morning orders
And our business team defined the below ...
- 243
2
votes
3
answers
3k
views
How to optimize mysql group by with DATE_FORMAT
I have a sql.
select count(id) as total, DATE_FORMAT(create_time,"%Y-%m-%d") as create_date
from table_name
group by DATE_FORMAT(create_time,"%Y-%m-%d");
Then Definition of column ...
- 1,075
-1
votes
1
answer
82
views
Date Functions in MySQL
Looking for some help regarding mysql date functions.
I am looking to show data for a time period of today to 8 days ago (I have been using this line of code in mysql WHERE TIME > CURDATE() - ...
- 1
0
votes
3
answers
125
views
Get latest record from database based on month and date
I'm trying to get the latest record from a table based on the date. My date format is mm/yyyy. I've tried using MAX(date) but it's getting the record based on the month only. I've also tried using ...
- 197
-1
votes
1
answer
181
views
Get Birthday data date wise [duplicate]
I have a dob(DATE) field in table which stores birth dates of users. now i have query to get users who have birth date in this month is as follow
SELECT * FROM register WHERE MONTH(dob) = MONTH(NOW())...
user9341437
0
votes
2
answers
220
views
MySql Convert Date from STR_TO_DATE date_format
How do you convert the following date Mar 7 2017 1:26:46:886AM to 2017-03-07 01:26:00
select DATE_FORMAT(STR_TO_DATE('Mar 7 2017 1:26:46:886AM', '%b-%d-%Y'), '%Y-%m-%d');
I keep getting null
- 1,597
13
votes
2
answers
520
views
Why do two MySQL dates in 1582 appear to be the same but comparison result is false?
I know that Gregorian calendar started on Oct 15th 1582, and during the transition from Julian calendar, 10 days had been dropped.
When I'm doing this query:
SELECT STR_TO_DATE('1582-10-05', '%Y-%m-...
- 765
0
votes
2
answers
230
views
How to compare two dates in MySql exluding the weekends(SAT,SUN)
I want to find the difference between two dates(dateStart, dateEnd) only for weekdays that are Mon - Fri in MySql query.
Here is my query which compares two dates and gives result if a week or more ...
- 436
0
votes
1
answer
331
views
How to convert OLE Automation Date to My sql date
How to convert OLE Automation Date to a Mysql standard date type?
- 133
1
vote
1
answer
262
views
MySQL get records for monthly invoice taking different length of months into account
I have a database table called subscriptions. Each row in the table has a created_at date, from this day on, every month (afterwards) the subscription must be invoiced.
So on 2017-05-29, all ...
- 3,208
2
votes
3
answers
2k
views
MySQL selecting week number and year causes problems with last week of year
I have a large dataset spanning several years and I am having problems getting a string such as "53-2016" (week 53 in 2016).
My query is:
SELECT
date,
DATE_FORMAT(date, "%v-%Y") AS week,
...
- 764
0
votes
2
answers
641
views
JSF: Insert String Date Of Month-Day-Year Into Mysql
I have Date in my java blue print that is format as month-day-year.
This is the code for it
public String getFormattedDate() {
return new SimpleDateFormat("MM-dd-yyyy").format(dueDate);
}
I ...
- 59
0
votes
0
answers
1k
views
How to copy changed format of date to another column in same table MySQL
I've got one table with two columns, one timestamp with timestamp and second with date in '%Y-%m-%d' format.
I need to get timestamp from timestamp column, convert it to '%Y-%m-%d' and put it into ...
0
votes
2
answers
6k
views
IFNULL on DATETIME doesn't work with comparisons
I have the following schema:
CREATE TABLE test (
colA DATETIME,
colB DATETIME
);
INSERT INTO test (colA, colB) VALUES ('2017-01-01', NULL);
Here's some SQL I'm trying to run on this schema:
...
- 16.7k
0
votes
2
answers
1k
views
How to convert a string 'YEARWEEK' with STR_TO_DATE to a correct date?
I am trying to convert values like 201701 to 2017-01-01 which is the first day of the first week of 2017, I have tried this
SELECT STR_TO_DATE('201701', '%Y%v')
because DATE_FORMAT(DATE, '%Y%v') ...
- 53k
3
votes
0
answers
212
views
STR_TO_DATE returns null in SELECTbut throws an error in UPDATE
I'm trying to convert strings in one column to dates in a second column. The dates have varying timezones, so the STR_TO_DATE function chokes a bit.
createdTime : Thu Oct 08 09:28:18 MDT 2009
I can ...
- 9,710
-2
votes
2
answers
300
views
Insert Date and Time only if > = Current Date
I have a field appointment_date and appointment_time and datatype is date and time respectively. The table name is appointment. What our teacher wanted was to be able to add appointment ONLY if it is ...
0
votes
1
answer
2k
views
Convert MMM-YY string into Sortable Date Format
So I've gone around the houses a bit with this and not got it to work...
I have a SELECT query which contains a VARCHAR column (alloc_mth) with dates in the MMM-YY format (e.g. Jan-15) which I want ...
- 285
0
votes
2
answers
121
views
How to change date format inside table in MySQL?
I want my DateOfBirth inside a Table to be displayed in dd/mm/yy format. I know we can retrieve dates in different format. But how could I change the format in the table itself?
- 79
0
votes
1
answer
57
views
Get the currently online employees using MySQL
I have employees working in different shifts and weekdays. Their shift details are saved in workshifts table in the following format.
WeekDay - Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, ...
- 978
1
vote
1
answer
1k
views
How to use current date in mysql query?
I have a database that contains a column with type - Date. I also have a query with the date inputted as static which works fine but I would like to use todays date in the query. any recommendations?
...
- 145
0
votes
2
answers
79
views
Pair to spend longest time in a single Sql query
I have a long table with following columns:
Id(Serial), FirstName(varchar15), LastName(varchar15), StartDate(date), EndDate(date)
and the entries are like:
* 1, Amar, XoXo, 2009-07-01, 2014-05-23.
*...
- 2,233
1
vote
1
answer
996
views
Grouping DATETIME by DATE in MySQL and showing the last record of the day
I have a group of currency quotations that change every hour or so. For the sql results in this particular example, I only need the last available record of each day of the last 30 days in yyyy-mm-dd ...
- 11k
13
votes
3
answers
10k
views
Curdate() Vs current_date MySql
What is the difference between curdate() and current_date in MySQL?
- 131
0
votes
1
answer
227
views
How to subtract to previous year, but add months in MySQL?
I am new to procedural programming and mysql, but suppose I have a function PrevMonth(in_date date, in_mn_count int) The first is a date and the second is an integer which is expected to be the number ...
- 11
0
votes
2
answers
155
views
how to get the last three months of last year using mysql?
How would I go about retrieving records from the last three months of the previous year? I was thinking it would be:
Date_add(curdate() , interval '-1 2' year_month)
- 11
42
votes
5
answers
54k
views
How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?
I want to perform a query which would look like this in native SQL:
SELECT
AVG(t.column) AS average_value
FROM
table t
WHERE
YEAR(t.timestamp) = 2013 AND
MONTH(t.timestamp) = 09 AND
...
- 9,542
1
vote
2
answers
5k
views
MySQL query to update records with incremented date
I am trying to
get the latest date in a database, and
based on that date update every record that has a NULL date, increasing the date by 1 day.
I can get the latest date by using the Latest Date ...
- 6,353
1
vote
1
answer
134
views
How to use curtime() to check in column with now() structure? SQL
In my DB when a new user register I use now() for the column date_added (I need the exact time of registration). Also I store the user ip in column ip
Now I need to check in SQL if there is already ...
- 145
3
votes
2
answers
298
views
MySQL Index on date field
I'm using MySQL database. In that i'm having sales datas in one table and i've created an index for the Date column (i.e) OrderedDate.
The datas are retrieved fast when we use the query like
SELECT ...
- 727
20
votes
2
answers
122k
views
Mysql DATE_SUB(NOW(), INTERVAL 1 DAY) 24 hours or weekday?
I am trying to get the total amount of registered users per day. At the moment I am using this:
$sql = "SELECT name, email FROM users WHERE DATE_SUB(NOW(), INTERVAL 1 DAY) < lastModified"
...
- 4,914