Questions tagged [datetime]
for questions specific to data types that contain both date and time parts.
37 questions
18
votes
1
answer
45k
views
How do I generate a date series in PostgreSQL?
If you're looking to generate a time series, see this question
Let's say that I want to generate a series of dates between two dates.
I see the function generate_series provides only
Function ...
- 64.7k
3
votes
1
answer
2k
views
Conversion of varchar data to datetime failing
We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering
As part of this effort, we are taking data from the base Entity table and ...
- 16k
4
votes
5
answers
67k
views
Convert varchar column to datetime
In SQL Server, I have a varchar column with value such as:
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
07/May/2012:00:06:22
How do I convert this column ...
- 1,549
29
votes
1
answer
8k
views
What date/time literal formats are LANGUAGE and DATEFORMAT safe?
It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language:
yyyyMMdd ...
- 182k
8
votes
3
answers
7k
views
How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?
I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
6
votes
2
answers
28k
views
How to get minimum and maximum for grouped timestamps
I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each.
So a table that looks like this:
| ...
- 163
6
votes
1
answer
3k
views
What does 0 in DATEDIFF(MINUTE, 0, <Date>) actually mean?
So, our data team asked for some help in solving a problem they had. I eventually tracked it down to some really out of range data (1/1/0001) and a DATEDIFF function they were using. While I've ...
- 9,244
5
votes
2
answers
2k
views
Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? [closed]
When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For ...
- 153
5
votes
1
answer
2k
views
Leap second in Database system (Postgresql and SQL Server)
This night will be longer because of leap second: https://en.wikipedia.org/wiki/Leap_second
My impact will be minimal in my case, I don't even know if I will recive some weird data like 20150630 23:...
- 1,033
4
votes
3
answers
3k
views
MySQL: "FROM_UNIXTIME(0)" gives "1970-01-01 01:00:00", which is 1 hour off
Context: MySQL version
On MySQL 5.7.17 (as shown by SHOW VARIABLES LIKE "%version%";)
Context: MySQL timezone settings
and with SELECT @@global.time_zone, @@session.time_zone; yielding
+-----------...
- 197
0
votes
1
answer
1k
views
Why does the now() function return the same value for multiple inserted rows?
Perhaps there's a more idiomatic approach to this in Postgres, but the pattern is reasonably common so I expected it to work.
Here's the code:
drop table if exists tbl;
create temporary table tbl as ...
- 316
29
votes
6
answers
50k
views
Preferred way to store DateTime
We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information?
Storing Date and Time in 2 separate columns or one column using DateTime?
Can ...
- 452
21
votes
4
answers
52k
views
Why does my query search datetime not match?
select *
from A
where posted_date >= '2015-07-27 00:00:00.000'
and posted_date <= '2015-07-27 23:59:59.999'
But the result contains a record that has posted_date today: 2015-07-28.
My ...
- 283
17
votes
1
answer
127k
views
MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292
I'm using MySQL 5.6 and I have a program that runs the following SQL statement against my database:
UPDATE `m_table` SET `s_time` = '2013-08-25T17:00:00+00:00' WHERE id = '123'
Unforutnately, I get ...
- 273
15
votes
2
answers
3k
views
Incorrect comparison of datetime and datetime2
I know that it is not a good practice to have an implicit type convert. But this is really unexpected behavior when a lower value can suddenly become higher.
declare @LastSelectedDate DATETIME = '2021-...
- 1,533
14
votes
2
answers
10k
views
Handling time zones in data mart/warehouse
We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
11
votes
1
answer
5k
views
Why is GETUTCDATE earlier than SYSDATETIMEOFFSET?
Alternatively, how did Microsoft make time travel possible?
Consider this code:
DECLARE @Offset datetimeoffset = sysdatetimeoffset();
DECLARE @UTC datetime = getUTCdate();
DECLARE @UTCFromOffset ...
- 1,953
9
votes
5
answers
226k
views
Conversion of a varchar data type to a datetime data type resulted in an out-of-range value
I am trying to run a simple query to get all rows created in November:
SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN '2014-11-01 00:00:00.000'
AND '2014-11-30 23:59:59.997';
SMSS ...
- 93
9
votes
2
answers
803
views
DATEDIFF Rounding
Implementing a rotating partition scheme, per kejser.org/table-pattern-rotating-log-ring-. Ran into an issue with DATEDIFF rounding up values:
DECLARE @Partitions INT = 15;
SELECT
a1.dt
, ...
- 31.9k
8
votes
4
answers
85k
views
get all dates in the current month
i have a query in MySQL which serves me very well by getting all the records within the current month;
SELECT date_field,val FROM MY_TABLE WHERE date_field>=(CURDATE()-INTERVAL 1 MONTH);
The ...
- 545
7
votes
1
answer
19k
views
How to check the timezone for a given datetime?
A database / table I'm working it saves all its date values as datetime (without a timezone).
How can I find out which timezone that date was in?
For instance, the Central European Summer Time (+02) ...
- 171
5
votes
1
answer
4k
views
SQL Server 2005 - Best way to constraint a Datetime field as Date
Whereas:
date field type starting with SQL Server 2008
Given a table:
CREATE TABLE dbo.MyTable
(
Code int NOT NULL,
DateCode datetime NOT NULL
)
CONSTRAINT [PK_MyTable] PRIMARY KEY ...
- 23.9k
3
votes
3
answers
3k
views
Is timestamptz preferred when timezone logic is performed by the client?
I'm in the process of migrating a web application from SqlServer to PostgreSQL and I'm trying to figure out which type to replace datetime2 with.
The general advice seems to be always use timestamptz,...
- 175
3
votes
2
answers
40k
views
Should I join datetime to a date using cast or range?
This question is a take-off from the excellent one posed here:
Cast to date is sargable but is it a good idea?
In my case, I am not concerned with the WHERE clause but in joining to an events table ...
- 1,693
3
votes
2
answers
8k
views
Converting string to date with timezone adjustment
I am attempting to convert stirngs to dates in mysql
Thu Oct 23 16:46:47 2014 +02:00
Im unsure how to handle the +02:00, The rest is simple enough
update logs
set date = str_to_date(date_raw, '%a ...
3
votes
1
answer
7k
views
How to find XXth day of previous month in SQL server?
I have 2 parameters,
an input date (DATETIME)
input day of month (TINYINT)
If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the output ...
- 255
3
votes
1
answer
2k
views
TSQL DateTime DataType - fractional seconds vs milliseconds
Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000 and 2013-03-05 23:59:59.999. We're getting overlap at the backend with things that happened at 2013-03-06 00:...
- 3,416
2
votes
1
answer
1k
views
Office Hours in PostgreSQL
How to store office hours in PostgreSQL rows, so that I can calculate the office hours.
Example:
We have open from 9:00 to 18:00 from Monday till Friday.
Saturday we have open from 10:00 to 15:00
...
- 1,561
1
vote
2
answers
6k
views
Find the OverLapped (Common) Date Range from Group of Date Ranges
How can I find the Overlapped (common date range) from a group of Date Ranges given?
The following table contains events and programmes (EID and PID respectively)
CREATE TABLE #EventsTBL
(
PID ...
1
vote
0
answers
57
views
DateTime to Format: Implicit Conversion [duplicate]
I have a datetime I want to convert to a formatted date and time. The format is 109(9). I have always used:
CONVERT(char(26), c1.DateTime, 109)
Which works successfully, but at least on SQL Server ...
- 465
1
vote
1
answer
2k
views
Index Optimization for Datetime comparing day of week and hour
I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int, and to fill in gaps with data we have models by day of week and hour of day: model_id int, hour_of_day int, ...
- 559
1
vote
2
answers
250
views
PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)
Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations:
change timestamp to char(19)
tinyint to ENUM (I noticed it's overzealous with ...
- 473
1
vote
1
answer
688
views
Why do those Update statements fail using a datetime format?
Given:
Query #1:
begin tran
UPDATE [dbo].[t] SET [mydatetime]='2011-12-25 07:00:00.000';
rollback
Query #2: (changing month/day)
begin tran
UPDATE [dbo].[t] SET [mydatetime]='2011-25-12 07:00:00....
- 4,807
1
vote
1
answer
857
views
Help with adding a time filter to Postgres query
I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes...
I know Postgres is brilliant for grouping points based on their spatial values - that I can do -...
- 11
1
vote
2
answers
3k
views
What happens when we take a timediff between timestamp and datetime
Say I have following table in mysql (5.5+):
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL,
`x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`y_time`...
- 111
0
votes
2
answers
688
views
Converting from char(26) to datetime2
In course of a change I migrated a database from Oracle 12.1 to MSSQL 2016.
Since the two databases handle date differently, I now need to move the fields from char(26) to datetime2.
Output format is ...
- 1
0
votes
1
answer
499
views
What exactly is the datatype input into a DATETIME value in MySQL
I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right ...
- 105