Questions tagged [date]

Questions related to date or date-related functions, data-types, representations (how dates might be stored and displayed) and operations (such as subtracting dates, adding intervals to dates).

Filter by
Sorted by
Tagged with
1
vote
1answer
63 views

Casting int YYYYMMDD to date produces incorrect value in Squirrel-SQL

I have extracted some data from a legacy database where dates are stored as Numeric(8) values, in YYYYMMDD format. I've stored them in a staging table in that same format, and now I would like to ...
0
votes
1answer
15 views

How to select Overdue Rows with Date Frequencies?

+------------------------+--------+ | Invoice_id | due_date | amount | +-------------+----------+--------+ | 20 |2020-01-18| 1250 | +-------------+----------+--------+ | 21 |2020-...
-2
votes
1answer
18 views

Can't manage to work with comparing days

In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed. I found some answers on the Internet but they all are at least 5 years ago with ...
0
votes
1answer
25 views

MySQL CURDATE() and CURRENT_TIMESTAMP return different value?

I am facing a little confusion because of CURRENT_DATE value and CURRENT_TIMESTAMP value shows different date on MySQL. Scenario : 1) User calls API from Lumen backend to insert data on a table. One ...
0
votes
1answer
34 views

Most efficient way to query a date range in Postgresql

I have a table with a timestamp with tz column. I want to perform a count / group by query for rows within a certain date range: select count(1), url from app_pageview where viewed_at between '2019-...
0
votes
2answers
63 views

MySQL Coalesce date missing results, why?

Running MySQL 8.0.16, settings left at defaults as far as I know. I have a table, let's call it the_table, with columns id: bigint primary key auto_increment the_time: datetime I have a record ...
0
votes
3answers
44 views

Retrieving users by their age efficiently

I have a PostgreSQL database of users and their birthdays and want to get all users in a specific age range: SELECT * FROM users WHERE age(birthday) >= 21 AND age(birthday) <= 30; How can I ...
-2
votes
1answer
48 views

Working with NULL Date Fields [closed]

I have a derived VARCHAR date field that I need to use for deriving another field, based off of whether or not the date field has a value. The derived date field is called "AmortDate" and I'm ...
1
vote
1answer
22 views

MySQL wrong use of index when a date interval is big

I have a query that have this statement: SELECT DISTINCT C.id_produto FROM movdiario C INNER JOIN PRODUTO P ON C.ID_PRODUTO = P.CODITEM INNER JOIN estoques B ON B.id_produto=C.id_produto ...
0
votes
1answer
50 views

How do I form an SQL query to show entire column of dates with modified values?

Assume that I have a table Purchases with column named PurchaseDate and with data format of 'YYYY-MM-DD HH:MI:SS'. Suppose that I want to see the entire column of dates with the days increasing by 5. ...
0
votes
1answer
34 views

Locating all records relevant for a specific date

I have a MySQL InnoDB table with about 1GB of records and growing. each record in this table has, among other fields two date fields : AddedDateRecord - The date the record was added (datetime). ...
-2
votes
1answer
58 views

Calculate nth business day of the current month

Looking for a simple inline statement (MSSQL) that can return the current workday number of the month (number of days elapsed minus Saturday and Sunday) E.g. NOV 11 2019 would be the 7th working day.
0
votes
2answers
45 views

How to add time from a Date value to another without a varchar conversion?

Let's say I have 2 variables in a Stored Procedure, both are Dates Foo contains '13/10/2019 00:00:00' Bar contains '01/10/1914 16:33:11' I want to add Bar's time to Foo's date, so that I get a ...
0
votes
1answer
15 views

How to ensure all records of a header are under the same fiscal year?

Say I have the following schema: BUDGET_YEAR_TARGET INTEGER ID BUDGET_DATE_TARGET INTEGER ID INTEGER BUDGET_YEAR_TARGET_ID--Foreign key DATE DATE How can I ensure that any records ...
1
vote
2answers
42 views

MYSQL: How to optimize a date sorted table with a triple primary key index

I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began ...
0
votes
2answers
47 views

Fill in missing dates for a vacation schedule

Have an issue that has been driving me nuts for the past week and can't seem to get my head around it. I have a stored procedure that grabs a bunch of vacation requests from our scheduling system. I ...
0
votes
1answer
47 views

Performance MySQL query with for loop

In this method, first I have to get sundays dates between two dates, in this case about 1 year. Then I go through the dates in a for loop and set them to the query. I use prepared statements to make ...
0
votes
1answer
238 views

How to alter database hour and date in Oracle?

How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.
0
votes
0answers
26 views

Create range from one date

So, I have a specific problem, that I can't solve without a function. Here's a simple code I wrote which if can be made to work, will solve my bigger issue. with dates as ( select year_start, day ...
1
vote
2answers
75 views

Equivalent of Oracle RR date format in SQL Server

Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server. The Oracle export has date formats like this: to_date(...
0
votes
1answer
35 views

Best way to put date range in a table in mySQL

I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in ...
1
vote
2answers
63 views

Setting a date dynamically

I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it. The query is doing a count of the number of rows with an entered on date >= 1st August ...
0
votes
1answer
113 views

Sharding a MongoDB instance by a spatial key and a date

This may be more an advice than anything else, but I'm designing an app that stores in a table objects that have a 2dsphere index and they also have a date (+time), both of them being part of queries ...
0
votes
1answer
34 views

Column with Default value as Sum of TIMESTAMP

I have a table which has 2 columns ( JobDate , RecordTime) -------------------------------------- JobDate | RecordTime | SumCol -------------------------------------- 2019-07-20 | 2019-07-...
0
votes
0answers
72 views

How to group by a field in a certain time range in Cassandra

I have a big Cassandra table with around 15 fields, and I wanted to create many materialized views to support all of my queries. The Primary Key on the base table is (CompanyName, ctime) where ctime ...
0
votes
0answers
32 views

Redshift/Athena: Most efficient way to cast timestamps of varying precision to date in query?

I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the ...
3
votes
1answer
65 views

Fill missing dates within groups

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
1
vote
1answer
33 views

MariaDB CONNECT engine BIN table - file record layout, storage of a date data type, padding

In mariadb-10.4.5, running on Windows 10, I created this table (in the local database): `CREATE TABLE IF NOT EXISTS `testbalNoFieldFormat` ( `fig` int(4) NOT NULL, `name` char(10) NOT NULL, `...
-1
votes
1answer
23 views

Why am I not able to filter rows based on date? [closed]

I am trying to apply a filter on dates which is a string type but I am not able to get the desired results. for eg, select * from table_1 where date between '01-01-2016' and '31-12-2016'; The ...
0
votes
1answer
105 views

Unable to use today's date as a criteria in a query

Ok, been a good decade since I've worked with Access and SQL, so a little forgiveness please. Short version - when I add the query criteria to only display rows which match today's date, the query ...
1
vote
1answer
57 views

Convert Date and Filter by Year

I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field. For example, in my table there is a "Posting Date" field that is in the '20180401' ...
0
votes
2answers
959 views

Is it possible to write a SELECT statement to obtain all dates between 2 date columns?

My question is related to these 2 questions: https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates/27319120 https://stackoverflow.com/questions/1378593/get-a-list-of-...
0
votes
1answer
174 views

How to convert timestamp with time zone to numeric (unix) in PostgreSQL?

I have timestamps with time zone (timestamptz) in my table. Can I convert them to numeric or int in unix timestamp format? I found this question, but it shows only how to convert timestamp without ...
0
votes
0answers
26 views

MySQL date_format German date value not change to date type

I have a column that is varchar and the value of the the data is a date (i.e. 31.05.2019) I'm trying to convert the varchar field to a date field using the following function: date_format(...
0
votes
1answer
55 views

Row for each date, start and end

I have a table that has two columns, startDate and endDate. Each day has its own capacity. Between these dates, user can get from 1 to n items. I am going to get number of items remaining for each day ...
1
vote
3answers
112 views

Count Items not In Time Range

I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out. So, I have this table: WITH ADates AS ( ...
0
votes
2answers
36 views

Date variable to filter result for all previous years

I am querying a sales order table and need to add a variable so that the query always returns a customer list for all customers that have not purchased this year. I don't want to hard code this years'...
2
votes
1answer
550 views

Oracle: Casting timestamp as date and comparing result

I don‘t really understand what cast(timestamp as date) is doing. I am on Oracle Database 11g Express Edition Release 11.2.0.2.0. select cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') ...
0
votes
1answer
38 views

Why does year 0 become year 2000?

select date '0-1-1'; 2000-01-01 Year 0 doesn't exist according to Wikipedia, so shouldn't Postgresql give an error instead of jumping to 2000? Year zero does not exist in the anno Domini system ...
2
votes
2answers
47 views

Filter Table by a Historical 'Last Day of the Month'

I have a daily appending table, that I need a formula for that would filter the table results to just the last day of any given historical month. For example: Table: DwDate Balance ...
1
vote
1answer
104 views

How to get all dates belonging to a timestamp range

I have a tsrange as input. For example: tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp) I want to generate all dates belonging to this range. I started with the following, ...
1
vote
1answer
63 views

Subtracting Days From Current Date

I know this is probably very basic, but I'm trying to create a calculated field that subtracts X number of from Current Date, based off of the value of another field: CASE WHEN DaysPD = 30 THEN 1 ...
-1
votes
1answer
190 views

Convert SQL Date MMDDYY varchar to Date?

In reading this, https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 http://www.sqlusa.com/bestpractices/datetimeconversion/ I cannot locate the ...
1
vote
1answer
35 views

calculate days spent of all visitors from encounters

I have an encounters table CREATE TABLE visitors( id serial PRIMARY KEY, start_date TIMESTAMP NOT NULL, -- visitor_id bigint NOT NULL -- skip for now, end_date TIMESTAMP ); (end_date can be ...
2
votes
1answer
511 views

Splitting a year into pieces

I want to split a given year into given number of date range pieces using SQL. Following points need to be considered while splitting it: Given year will always start from 01-01 and end at 12-31 and ...
1
vote
1answer
62 views

SQL Server 2008 onward - Globalization with Date Time

My application is standalone windows application. Database is SQL Server 2008 onward. The application will be deployed on systems around the globe. User should view the date/time values in their own ...
1
vote
1answer
123 views

Get closest previous date

I need to get the posts closest to a date. SELECT id , datetime FROM dates WHERE id != 150003 AND datetime > FROM_UNIXTIME(1549622615) ORDER BY datetime LIMIT 1 Returns a correct ...
2
votes
2answers
536 views

how to count distinct value per day with start time and end time

my title may seem a bit confusing but here's the context. I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user ...
-3
votes
1answer
44 views

Can we get any performance gain from creating index from date column?

I have seen lot of indexes created on the date columns in my project. Normally the index is a combination of a date column and another column (like state) which are used to create an index. Since ...
0
votes
1answer
37 views

Get total row by month in multiple tables [closed]

I am new in Oracle database and php, I tried to create a monthly report which covers the whole 12 months of a year. Currently I have 5 different tables which will store data every month for one year. ...