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
0
votes
2answers
42 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
26 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
72 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
22 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
39 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
29 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
59 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
26 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
29 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
21 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
13 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
36 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
30 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
21 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
81 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
49 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
174 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
83 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
17 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
52 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 ...
2
votes
3answers
111 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
35 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
150 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
36 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
40 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
77 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
47 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
59 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
29 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
317 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
46 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
82 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
366 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
35 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. ...
0
votes
2answers
73 views

How can I use mysql to determine the Sundays for the last year?

I have a table where I'm currently aggregating ~200 rows per day of stats. These stats include things like # of users, how many people using X feature, etc. What I'd like to do is query this table ...
1
vote
2answers
443 views

Insert missing dates within table

I have a table with a column for ID, Date and Frequency of occurrence for that particular date and ID. ID date Frequency 1 2014-05-18 5 1 2014-05-19 4 1 2014-05-20 ...
1
vote
1answer
158 views

Select within or closest to date range MSSQL

Lets say we have a table CREATE TABLE [dbo].[Product]( [ProductId] [int] NOT NULL, [Name] [varchar](50) NOT NULL ) CREATE TABLE [ProductPrice]( [ProductPriceId] [int] NOT NULL, [...
0
votes
0answers
10 views

Creating a database for due dates

In my current role I have to write down all my projects on pre-made grid on a dry erase board. As I finish a project I erase it but as the board reaches its capacity I have to manually shift ...
0
votes
1answer
156 views

finding the most recently updated record with multiple date columns

I have recently inherited a SQL Server 2016 database which was converted from an old MS Access db. I have a table that shows various repair dates and test dates for associated serial numbers. I would ...
2
votes
2answers
122 views

Converting and Subtracting Two Dates

I'm trying to get the numeric difference between two dates. (Ex: 11/30/2018 - 11/05/2018 = 25) One of the dates (@EndOfMonth) represent the last day of the prior month and is being converted into a ...
2
votes
1answer
55 views

Sum values by time

id_ | date_ | hour_ | in_|out_| |…. | …. | …. | …. | …. |19 | 2018-12-12| 18:15:00| 1 | 3 | |19 | 2018-12-12| 18:30:00| 1 | 0 | |19 | 2018-12-12| 18:45:00| 2 | 4 | |...
0
votes
2answers
75 views

Getting Records with Min Max Value on Grouped in Single Table in postgresql

The table looks like this: Existing Table: table_1 Desired Result: table_1 I have been able to get result like the following using this code: table_1 But I would like to get minimum and maximum ...
0
votes
1answer
92 views

how to convert a date string like this MM/YYYY to 01-MM-YY

I have a column and its type is string and stores date something like this 03/1996 I want to convert that to 01-MAR-96? how can I achieve that in ORACLE
0
votes
1answer
708 views

Generate Dates between Date Ranges in mysql

Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9. Is there an inbuilt function which I ...
5
votes
3answers
286 views

Getting SQL server to recognise a date column

I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed. My table looks like this orders ( order_id INT ...
3
votes
1answer
8k views

Select data from the previous 3 months [duplicate]

I need help selecting data where the date is in the previous 3 months. (note: I'm not asking for the last 3 months i.e. DATEADD(MONTH, -3, GETDATE()) ) The reason I don't want to use the DATEADD ...
-1
votes
1answer
33 views

MySQL - Classic Date Functions

Version: 10.2.18-MariaDB I'm attempting to find the first available room within December (already included in the sample table) that will be free for 3 consecutive days. The expected output should ...
1
vote
1answer
41 views

Searching through dates query not working?

I'm trying to query the recent week rows by using dates. BUT the query is not returning any result. It takes forever to execute. I checked my DataBase where date is saved in 1446976737 format which ...
0
votes
1answer
55 views

SAP BO Freehand SQL date formatting (Oracle db)

My original query worked perfectly via SQL Developer: select * from [table] where DT_CURRENT_DT = '15-OCT-18' However when ran via freehand SQL in SAP BO I got no results. After running the query ...